package com.seeyon.apps.ltc.manager.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.aspose.imaging.internal.I.O;
import com.seeyon.apps.brwh.kit.DateUtil;
import com.seeyon.apps.brwh.kit.StrKit;
import com.seeyon.apps.brwh.kit.FormCap4Kit;
import com.seeyon.apps.brwh.kit.StrKit;
import com.seeyon.apps.ltc.manager.LtcKanBanDataManager;
import com.seeyon.cap4.form.bean.FormBean;
import com.seeyon.cap4.form.bean.FormFieldBean;
import com.seeyon.cap4.form.bean.FormFieldBean;
import com.seeyon.cap4.form.bean.FormTableBean;
import com.seeyon.cap4.form.modules.engin.manager.CapFormDefinitionManager;
import com.seeyon.cap4.form.po.CAPFormDefinition;
import com.seeyon.cap4.form.service.CAP4FormManager;
import com.seeyon.cap4.template.manager.CAPDataListSqlHelpManager;
import com.seeyon.ctp.common.AppContext;
import com.seeyon.ctp.common.ctpenumnew.manager.EnumManager;
import com.seeyon.ctp.common.exceptions.BusinessException;
import com.seeyon.ctp.common.log.CtpLogFactory;
import com.seeyon.ctp.common.po.ctpenumnew.CtpEnumItem;
import com.seeyon.ctp.common.po.template.CtpTemplate;
import com.seeyon.ctp.common.template.manager.TemplateManager;

import java.sql.SQLException;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.text.ParseException;
import java.util.HashMap;

import com.seeyon.ctp.common.po.ctpenumnew.CtpEnumItem;
import com.seeyon.ctp.organization.bo.V3xOrgDepartment;
import com.seeyon.ctp.organization.bo.V3xOrgMember;
import com.seeyon.ctp.organization.manager.OrgManager;
import com.seeyon.ctp.util.ObjectUtil;
import com.seeyon.ctp.util.StringUtil;

import org.apache.commons.logging.Log;
import org.springframework.util.ObjectUtils;

/**
 * @author lw
 * @description: ltc看板数据实现类
 * @date 2024年09月09日
 */
public class LtcKanBanDataManagerImpl implements LtcKanBanDataManager {

    private static final Log log = CtpLogFactory.getLog(LtcKanBanDataManagerImpl.class);
    private CAP4FormManager cap4FormManager = (CAP4FormManager) AppContext.getBean("cap4FormManager");
    private CAPDataListSqlHelpManager capDataListSqlHelpManager = (CAPDataListSqlHelpManager) AppContext.getBean("capDataListSqlHelpManager");
    private static EnumManager enumManagerNew = (EnumManager) AppContext.getBean("enumManagerNew");
    private static TemplateManager templateManager = (TemplateManager) AppContext.getBean("templateManager");

    private static EnumManager enumManager = (EnumManager) AppContext.getBean("enumManagerNew");

    private OrgManager orgManager = (OrgManager) AppContext.getBean("orgManager");


    private static final String DATABASETYPE = "sqlServer";




    private CapFormDefinitionManager capFormDefinitionManager = (CapFormDefinitionManager)AppContext.getBean("capFormDefinitionManager");
    private Map<String, Integer> levelCounts;

    @Override
    public List<Map<String, Object>> getProjectInfoDataList(String projectName, String level,String proManagerName,String cusManagerName, String unitId) {
        /* 获取系统属性中的项目相关字段描述 */


        /* 初始化需要的数据 */
        List<Map<String, Object>> resultDataList = new ArrayList<Map<String, Object>>();
        String tableName="";
        String source="";
        String proCode="";
        String proName="";
        String projectLevel="";
        String projectManager="";
        String customersManager="";
        String productsManager="";
        String developStatu="";
        String oadh="";
        String unit="";
        String doucumentLevel="";

        /*明细表字段*/
        String isLcb="";
        String lcb="";
        String planDate="";
        String trueDate="";
        String chargePerson="";
        String mainPlanStatu="";
        String childPlanOwnerMilestone="";
        String ipdLTCStage="";
        String ltcLTCPlanStatus="";
        String ltcLTCSmainPlan="";
        String ipdLTCStatu="";
        /*明细表名*/
        String zjhTableName="";
        String ipdTableName="";
        String childPlanTableName="";



        try {
            /* 初始化结果数据列表 */

            /*获取项目研发档案表中对应字段*/
            String yfdd_formCode = AppContext.getSystemProperty("ltc.yfxmda_formCode");
            FormBean formBean = cap4FormManager.getFormByFormCode(yfdd_formCode);
            if (formBean!=null){
                tableName=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_kfxmbm")).getOwnerTableName();
                /*研发项目编码*/
                proCode=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_kfxmbm")).getName();
                /*研发项目名称*/
                proName=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_xsxmmc")).getName();
                /*项目级别*/
                projectLevel=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_xqpj")).getName();
                /*项目经理*/
                projectManager = formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_xmjl")).getName();
                /*客户经理*/
                customersManager=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_khjl")).getName();
                /*产品经理*/
                productsManager=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_cpjl")).getName();
                /*ltc开发状态*/
                developStatu=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_ltckfzt")).getName();
                /*所属销售部门*/
                //oadh=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_yfxmdoadh")).getName();
                unit=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_unit")).getName();
                /*单据类型*/
                doucumentLevel=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_pjxq")).getName();

                // ltc主计划表名
                zjhTableName=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_chargePerson")).getOwnerTableName();
                /*是否里程碑*/
                isLcb=formBean.getFieldBeanByDisplay("是否里程碑").getName();
                /*里程碑*/
                lcb=formBean.getFieldBeanByDisplay("LTC里程碑").getName();
                /*计划结束时间*/
                planDate=formBean.getFieldBeanByDisplay("LTC计划结束时间").getName();
                /*实际完成时间*/
                trueDate=formBean.getFieldBeanByDisplay("LTC计划结束时间").getName();
                /*责任人*/
                chargePerson=formBean.getFieldBeanByDisplay("LTC责任人").getName();
                //实际计划状态
                mainPlanStatu=formBean.getFieldBeanByDisplay("实际计划状态").getName();

                /*明细表IPD计划*/
                //LTC阶段
                ipdLTCStage=formBean.getFieldBeanByDisplay("LTC阶段").getName();
                //计划状态-项目主计划
                ipdLTCStatu=formBean.getFieldBeanByDisplay("计划状态-项目主计划").getName();

                //明细表IPD计划表名
                ipdTableName=formBean.getFieldBeanByDisplay("计划状态-项目主计划").getOwnerTableName();

                /*LTC子计划*/
                childPlanOwnerMilestone=formBean.getFieldBeanByDisplay("所属里程碑").getName();
                //LTC计划状态-子计划
                ltcLTCPlanStatus=formBean.getFieldBeanByDisplay("LTC计划状态-子计划").getName();
                //LTC子计划表名
                childPlanTableName=formBean.getFieldBeanByDisplay("LTC计划状态-子计划").getOwnerTableName();
                //LTC主计划
               ltcLTCSmainPlan=formBean.getFieldBeanByDisplay("LTC主计划").getName();

                //封装查询LTC销售项目WBS档案主数据查询sql
                StringBuilder sql = new StringBuilder();
                sql.append("select id as id, ");
                /*当获取到的字段不为空时，拼接进入sql*/
                List<String> fields = new ArrayList<>();

                if (!"".equals(proCode)) {
                    fields.add(proCode);
                }
                if (!"".equals(proName)) {
                    fields.add(proName);
                }
                if (!"".equals(projectLevel)) {
                    fields.add(projectLevel);

                }
                if (!"".equals(projectManager)) {
                    fields.add(projectManager);
                }
                if (!"".equals(customersManager)) {
                    fields.add(customersManager);
                }
                if (!"".equals(productsManager)) {
                    fields.add(productsManager);
                }
                if (!"".equals(developStatu)) {
                    fields.add(developStatu);
                }
                if (!"".equals(unit)) {
                    fields.add(unit);
                }


                // 将所有非空字段添加到 SQL 语句中
                for (int i = 0; i < fields.size(); i++) {
                    sql.append(fields.get(i));
                    if (i < fields.size() - 1) {
                        sql.append(", ");
                    }
                }
                //根据入参处理模糊查询，在sql品拼接条件
                sql.append(" from ").append(tableName).append(" where ");

                boolean firstCondition = true;
                int appendCount=0;
                //根据项目名称筛选
                if (!"".equals(projectName)) {
                    if (!firstCondition) {
                        sql.append(" and ");
                    }
                    sql.append(proName).append(" like '%").append(projectName).append("%'");
                    firstCondition = false;
                    appendCount++;
                }
                //根据项目星级筛选
                if (!"".equals(level)) {
                    if (!firstCondition) {
                        sql.append(" and ");
                    }
                    sql.append(projectLevel).append(" = ").append(level);
                    firstCondition = false;
                    appendCount++;
                }
                //根据项目经理筛选
                if (!"".equals(proManagerName)) {
                    if (!firstCondition) {
                        sql.append(" and ");
                    }
                    sql.append(projectManager).append(" = ").append(proManagerName);
                    firstCondition = false;
                    appendCount++;
                }
                //根据客户经理筛选
                if (!"".equals(cusManagerName)) {
                    if (!firstCondition) {
                        sql.append(" and ");
                    }
                    sql.append(customersManager).append(" = ").append(cusManagerName);
                    firstCondition = false;
                    appendCount++;
                }

                //根据填报部门筛选
                if (!"".equals(unitId)) {
                    if (!firstCondition) {
                        sql.append(" and ");
                    }
                    sql.append(unit).append(" = ").append(unitId);
                    firstCondition = false;
                    appendCount++;
                }

                 //如果没有条件，则添加一个始终成立的条件
                if (appendCount!=0) {
                        sql.append(" and  ");
                }
                if(doucumentLevel!="" && proCode!=null){
                    String documentTypeId=StrKit.str(FormCap4Kit.getEnumByCode("ltc").getId());
                    log.info("documentTypeId"+documentTypeId);
                    sql.append(doucumentLevel).append(" = ").append(documentTypeId).append(" order by ").append(proCode).append(" desc");
                }

                log.info("ltc项目信息查询SQL： " + sql.toString());
                List dataList = capDataListSqlHelpManager.selectDataList(sql.toString(), null);
                int sort=0;
                int typeSort=0;
                HashMap<Integer, Integer> innerSort = new HashMap<Integer, Integer>();
                ArrayList<HashMap<Integer,Integer>> innerList=new ArrayList<HashMap<Integer,Integer>>();
                //HashMap<String, Integer> sorMap=createSortArry(StrKit.str(AppContext.getSystemProperty("yfxmda_lcbmfjl")));


                LinkedList< Map<String, Object> >  ssaSet=new LinkedList< Map<String, Object> >();
                LinkedList< Map<String, Object> >  ssbSet=new LinkedList< Map<String, Object> >();
                LinkedList< Map<String, Object> >  aSet=new LinkedList< Map<String, Object> >();
                LinkedList< Map<String, Object> >  bSet=new LinkedList< Map<String, Object> >();
                LinkedList< Map<String, Object> >  cSet=new LinkedList< Map<String, Object> >();
                LinkedList< Map<String, Object> >  dSet=new LinkedList< Map<String, Object> >();
                LinkedList< Map<String, Object> > otherSet=new LinkedList< Map<String, Object> >();
                for (int i = 0; i < dataList.size(); i++) {
                    Map<String, Object> dataMap = (Map<String, Object>) dataList.get(i);
                    String masterId = StrKit.str(dataMap.get("id"));
                    Integer finnalSort=0;
                    /*根据项目星级排序*/
                    String levelId=StrKit.str(dataMap.get(projectLevel));
                    if (levelId==""){
                        otherSet.add(dataMap);
                        continue;
                    }
                    CtpEnumItem xmxjEnum = enumManager.getCtpEnumItem(Long.parseLong(levelId));
                    if (xmxjEnum==null){
                        otherSet.add(dataMap);
                        continue;
                    }
                    String showValue = xmxjEnum.getShowvalue();
                    if ("D级".equals(showValue)) {
                        dSet.add(dataMap);
                    } else if ("C级".equals(showValue)) {
                        cSet.add(dataMap);
                    } else if ("B级".equals(showValue)) {
                        bSet.add(dataMap);
                    } else if ("A级".equals(showValue)) {
                        aSet.add(dataMap);
                    } else if ("双十B级".equals(showValue)) {
                        ssbSet.add(dataMap);
                    } else if ("双十A级".equals(showValue)) {
                        ssaSet.add(dataMap);
                    }
                }
                /*组装list*/
                LinkedList< Map<String, Object> >  newList = new LinkedList< Map<String, Object> >();
                newList=addDataList(newList,ssaSet);
                newList=addDataList(newList,ssbSet);
                newList=addDataList(newList,aSet);
                newList=addDataList(newList,bSet);
                newList=addDataList(newList,cSet);
                newList=addDataList(newList,dSet);
                newList=addDataList(newList,otherSet);
                /*查询结果不为空，进行数据处理*/
                if (!newList.isEmpty()) {
                    for (int i = 0; i < newList.size(); i++) {
                        sort += 1;
                        Map<String, Object> resultDataMap = new HashMap<String, Object>();
                        Map<String, Object> dataMap = (Map<String, Object>) newList.get(i);
                        String masterId = StrKit.str(dataMap.get("id"));

                        String projectNameValue = StrKit.str(dataMap.get(proName));
                        /*档案表跳转url*/
                        /*获取跳转url参数*/
                        CAPFormDefinition capFormDefinition = capFormDefinitionManager.selectById(formBean.getId());
                        String appbindInfo = capFormDefinition.getAppbindInfo();
                        Map<String,Object> jsonObject = JSONObject.parseObject(appbindInfo,Map.class);
                        List<Map<String, Object>> formBindAuthList =  (List<Map<String, Object>>)jsonObject.get("formBindAuthList");
                        String tempId = StrKit.str(formBindAuthList.get(0).get("id"));
                        //String url="/seeyon/cap4/businessTemplateController.do?method=formContent&type=browse&rightId=-4413199312032414981.6365012735425791771&moduleId="+masterId+"&formTemplateId="+tempId+"&viewConditionId=&columnId=6602565548169644129&moduleType=42&conditionIdDeal=&needUpdate=1&tplId=850010";
                        /*正式环境url*/
                         String url="/seeyon/cap4/businessTemplateController.do?method=formContent&type=browse&rightId=-4509171917144241165.6752352808343652071_342945871049409636.7439910238220275328&moduleId="+masterId+"&formTemplateId="+tempId+"&viewConditionId=&columnId=4998888440732142007&moduleType=42&conditionIdDeal=&needUpdate=1&tplId=850010";
                        String projectCodeValue = StrKit.str(dataMap.get(proCode));
                        resultDataMap.put("projectCode", projectCodeValue);

                        /*根据开发状态值获取显示值*/
                        String kfStatuslValue = StrKit.str(dataMap.get(developStatu));
                        if (!"".equals(kfStatuslValue)) {
                            CtpEnumItem kfStatusEnum = enumManager.getCtpEnumItem(Long.parseLong(kfStatuslValue));
                            resultDataMap.put("kfStatus", kfStatusEnum.getShowvalue());
                            resultDataMap.put("kfStatusID",kfStatuslValue);
                        } else {
                            resultDataMap.put("kfStatus", "");
                            resultDataMap.put("kfStatusID", "");
                        }

                        /*根据开发来源值获取显示值*/
                        //String proSource = StrKit.str(dataMap.get(source));
                        //if (!"".equals(proSource)) {
                        //    CtpEnumItem proSourceEnum = enumManager.getCtpEnumItem(Long.parseLong(proSource));
                        //    resultDataMap.put("developmentSource", proSourceEnum.getShowvalue());
                        //} else {
                        //    resultDataMap.put("developmentSource", "");
                        //}
                        resultDataMap.put("url", url);
                        resultDataMap.put("sort", sort);
                        resultDataMap.put("projectName", projectNameValue);

                        /* 根据项目经理、客户经理和产品经理值获取名称 */
                        String projectManagerValue = StrKit.str(dataMap.get(projectManager));
                        if (!"".equals(projectManagerValue)) {
                            V3xOrgMember v3xOrgMember = orgManager.getMemberById(Long.parseLong(projectManagerValue));
                            resultDataMap.put("projectManager", v3xOrgMember.getName());
                            resultDataMap.put("projectManagerID", projectManagerValue);
                        } else {
                            resultDataMap.put("projectManager", "");
                            resultDataMap.put("projectManagerID", "");
                        }
                        /* 根据客户经理字段获取客户经理名称 */
                        String customerManagerValue = StrKit.str(dataMap.get(customersManager));
                        if (!"".equals(customerManagerValue)) {
                            V3xOrgMember v3xOrgMember = orgManager.getMemberById(Long.parseLong(customerManagerValue));
                            resultDataMap.put("customerManager", v3xOrgMember.getName());
                            resultDataMap.put("customerManagerID", customerManagerValue);
                        } else {
                            resultDataMap.put("customerManager", "");
                            resultDataMap.put("customerManagerID", "");
                        }

                        /* 根据产品经理字段获取产品经理名称 */
                        String productManagerValue = StrKit.str(dataMap.get(productsManager));
                        if (!"".equals(productManagerValue)) {
                            V3xOrgMember v3xOrgMember = orgManager.getMemberById(Long.parseLong(productManagerValue));
                            resultDataMap.put("productManager", v3xOrgMember.getName());
                            resultDataMap.put("productManagerID",productManagerValue);
                        } else {
                            resultDataMap.put("productManager", "");
                            resultDataMap.put("productManagerID", "");
                        }
                        /* 根据项目级别值获取显示值 */
                        String projectLevelValue = StrKit.str(dataMap.get(projectLevel));
                        if (!"".equals(projectLevelValue)) {
                            CtpEnumItem projectLevelEnum = enumManager.getCtpEnumItem(Long.parseLong(projectLevelValue));
                            resultDataMap.put("projectLevel", projectLevelEnum.getShowvalue());
                            resultDataMap.put("projectLevelID", projectLevelValue);
                        } else {
                            resultDataMap.put("projectLevel", "");
                            resultDataMap.put("projectLevelID", "");
                        }

                        /*明细表字段*/
//                        String isLcb="";
//                        String lcb="";
//                        String planDate="";
//                        String trueDate="";
                        /*明细表名*/
//                        String zjhTableName="";
                        /*处理是否历程和里程碑的枚举值*/
                        //项目主计划编制
                        //String xmzjhbz=StrKit.str( FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_xmzjhbz")).getId());
                        //标前引导策略制定
                        String bqydclzd=StrKit.str( FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_bqydclzd")).getId());
                        //客户确认标前引导方案
                        String khqrbqydfa=StrKit.str( FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_khqrbqydfa")).getId());
                        //解决方案策略制定
                        String jjfaclzd=StrKit.str( FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_jjfaclzd")).getId());
                        //ATB评审
                        String atbps=StrKit.str( FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_atbps")).getId());
                        //复盘
                        String lcbjdfp=StrKit.str( FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_lcbjdfp")).getId());
                        //合同谈判策略制定
                        String httpclzd=StrKit.str(FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_httpclzd")).getId());
                        //ATC
                        String atc=StrKit.str( FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_atc")).getId());
                        //组织合同交底
                        String zzhtjd=StrKit.str( FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_zzhtjd")).getId());
                        //是否里程碑
                        String sflcbEnumId=StrKit.str( FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_lcbjdsf")).getId());

                        dealSubData(new String("bqydclzd"),resultDataMap,planDate, trueDate, masterId,  lcb, bqydclzd, chargePerson,mainPlanStatu,childPlanOwnerMilestone,ipdLTCStage,ipdLTCStatu,ltcLTCPlanStatus,ltcLTCSmainPlan,zjhTableName,ipdTableName,childPlanTableName);
                        dealSubData(new String("khqrbqydfa"),resultDataMap,planDate, trueDate,  masterId,  lcb, khqrbqydfa, chargePerson,mainPlanStatu,childPlanOwnerMilestone,ipdLTCStage,ipdLTCStatu,ltcLTCPlanStatus,ltcLTCSmainPlan,zjhTableName,ipdTableName,childPlanTableName);
                        dealSubData(new String("jjfaclzd"),resultDataMap,planDate, trueDate,  masterId,   lcb, jjfaclzd, chargePerson,mainPlanStatu,childPlanOwnerMilestone,ipdLTCStage,ipdLTCStatu,ltcLTCPlanStatus,ltcLTCSmainPlan,zjhTableName,ipdTableName,childPlanTableName);
                        dealSubData(new String("atbps"),resultDataMap,planDate, trueDate,  masterId,   lcb, atbps, chargePerson,mainPlanStatu,childPlanOwnerMilestone,ipdLTCStage,ipdLTCStatu,ltcLTCPlanStatus,ltcLTCSmainPlan,zjhTableName,ipdTableName,childPlanTableName);
                        dealSubData(new String("lcbjdfp"),resultDataMap,planDate, trueDate,  masterId,   lcb, lcbjdfp, chargePerson,mainPlanStatu,childPlanOwnerMilestone,ipdLTCStage,ipdLTCStatu,ltcLTCPlanStatus,ltcLTCSmainPlan,zjhTableName,ipdTableName,childPlanTableName);
                        dealSubData(new String("httpclzd"),resultDataMap,planDate, trueDate, masterId,  lcb, httpclzd, chargePerson,mainPlanStatu,childPlanOwnerMilestone,ipdLTCStage,ipdLTCStatu,ltcLTCPlanStatus,ltcLTCSmainPlan,zjhTableName,ipdTableName,childPlanTableName);
                        dealSubData(new String("atc"),resultDataMap,planDate, trueDate,  masterId,   lcb, atc, chargePerson,mainPlanStatu,childPlanOwnerMilestone,ipdLTCStage,ipdLTCStatu,ltcLTCPlanStatus,ltcLTCSmainPlan,zjhTableName,ipdTableName,childPlanTableName);
                        dealSubData(new String("zzhtjd"),resultDataMap,planDate, trueDate,  masterId,   lcb, zzhtjd, chargePerson,mainPlanStatu,childPlanOwnerMilestone,ipdLTCStage,ipdLTCStatu,ltcLTCPlanStatus,ltcLTCSmainPlan,zjhTableName,ipdTableName,childPlanTableName);

                        /* 将结果数据映射添加到结果数据列表中 */
                        resultDataList.add(resultDataMap);
                    }

                }


            }

        }catch (Exception e){
            log.error("获取项目信息报错异常:", e);
        }

        return resultDataList;
    }

    /**
     * 将源数据列表添加到总数据列表中
     *
     * 该方法检查源数据列表是否为空如果源数据列表不为空，则将整个源数据列表作为一项添加到总数据列表中
     * 这个方法主要用于聚合数据，以便于后续处理
     *
     * @param allDataList 用于存储所有数据的链表此链表将包含从源数据列表添加的数据
     * @param sourceDataList 需要添加到总数据列表中的源数据链表
     * @return 返回更新后的总数据列表，包含新添加的源数据列表作为一项
     */
    private LinkedList addDataList(LinkedList allDataList, LinkedList sourceDataList) {
        if (!sourceDataList.isEmpty() && sourceDataList.size()!=0) {
         for(int i=0;i<sourceDataList.size();i++){
             Map<String, Object> map=(Map<String, Object>)sourceDataList.get(i);
             allDataList.add(map);
         }
        }
        return allDataList;
    }



    private void dealSubData( String returnName, Map<String, Object> resultDataMap, String planDate, String trueDate,  String masterId,
                               String lcb, String enumId, String chargePerson,String mainPlanStatu,String childPlanOwnerMilestone
    ,String ipdLTCStage,String ipdLTCStatu, String ltcLTCPlanStatus,String ltcLTCSmainPlan, String zjhTableName,String ipdTableName, String childPlanTableName) throws BusinessException, SQLException {

        //分子
        int numerator=0;
        //分母
        int denominator=1;
            // 拼接SQL查询语句，用于查询子计划数据
        String zjhSQL = "select " + planDate + " as plandata, " + trueDate + " as truedata, " + chargePerson + " as chargeperson from " + zjhTableName + " where formmain_id = '" + masterId + "' and " + lcb + " = '" + enumId + "'";
            //处理计划完成时间 实际完成时间 责任人
        List data = capDataListSqlHelpManager.selectDataList(zjhSQL, null);
        if (data.size()>0) {
                Map<String, Object> tpMap = (Map<String, Object>) data.get(0);
                String planDateValue = StrKit.str(tpMap.get("plandata"));
                String trueDateValue = StrKit.str(tpMap.get("truedata"));
                String chargepersonName = StrKit.str(tpMap.get("chargeperson"));
                //String chargepersonName="";
                //if(!chargeperson.equals(" ") && chargeperson!=null){
                //    V3xOrgMember member=orgManager.getMemberById(Long.parseLong(chargeperson));
                //    if (member!=null){
                //        chargepersonName=member.getName();
                //    }
                //}
                // 将处理后的数据放入结果Map中
                resultDataMap.put(returnName + "PlanDate", planDateValue);
                resultDataMap.put(returnName + "TrueDate", trueDateValue);
                resultDataMap.put(returnName + "Chargeperson", chargepersonName);
            }

        /**里程碑完成百分比逻辑
             * 1 分子：里程碑完成数量(0 or 1)+该历程所有完成的子计划数量+(标前引导/ATB评审) 前置任务已完成数量
             * 2 分母：1+该历程所有的子计划数量+(标前引导/ATB评审) 前置任务数量
             * 3.保留两位小数
         * */
        //里程碑编码
        String enumItemCode = enumManagerNew.getEnumItem(Long.parseLong(enumId)).getEnumItemCode();
        //ATB阶段
        String stageItemId = StrKit.str(FormCap4Kit.getEnumByCode("zhidinghetijiaojiejuefangan").getId());
        //   childPlanTableName  zjhTableName 枚举-已完成
        String mainPlanStatuYwc=StrKit.str(FormCap4Kit.getEnumByCode("JHZT_YWC").getId());
        //ipdTableName 枚举-已完成
        String ipdPlanStatuYwc=StrKit.str(FormCap4Kit.getEnumByCode("LCBZT_YWC").getId());

        /*已完成数量*/
        //// 里程碑完成数量查询sql
        //String mielsStoneEndSumSql="SELECT COUNT(1) AS ywc FROM "+zjhTableName+ " where formmain_id = '" + masterId + "' and " + lcb + " = '" + enumId
        //        + "' AND "+mainPlanStatu+" = '"+mainPlanStatuYwc+"'";
        ////该历程所有完成的子计划数量sql
        //String ywcChildEndSumSql ="SELECT COUNT(1) AS ywc FROM "+childPlanTableName+ " where formmain_id = '" + masterId + "' and " + childPlanOwnerMilestone + " = '" + enumId
        //        + "' AND "+ltcLTCPlanStatus+" = '"+mainPlanStatuYwc+"'";
        ////前置任务已完成数量
        //String ywcIpdEndSumSql="SELECT COUNT(1) AS ywc FROM "+ipdTableName+ " where formmain_id = '" + masterId + "' and " + ipdLTCStage + " = '" + stageItemId
        //        + "' AND "+ipdLTCStatu+" = '"+ipdPlanStatuYwc+"'";

        /*所有数量*/
        //// 里程碑完成数量查询sql
        //String allMielsStoneEndSumSql="SELECT COUNT(1) AS ywc FROM "+zjhTableName+ " where formmain_id = '" + masterId + "' and " + lcb + " = '" + enumId
        //        + "' ";
        ////该历程所有完成的子计划数量sql
        //String allChildEndSumSql ="SELECT COUNT(1) AS ywc FROM "+childPlanTableName+ " where formmain_id = '" + masterId + "' and " + childPlanOwnerMilestone + " = '" + enumId
        //        + "'";
        ////前置任务已完成数量
        //String allIpdEndSumSql="SELECT COUNT(1) AS ywc FROM "+ipdTableName+ " where formmain_id = '" + masterId + "' and " + ipdLTCStage + " = '" + stageItemId
        //        + "'";
        String ywcsql = "";

        if (!"ltc_atbps".equals(enumItemCode)) {

            ywcsql =
                    "SELECT SUM(ywc) AS total_ywc FROM (" +
                            "   SELECT COUNT(1) AS ywc FROM " + zjhTableName +
                            "   WHERE formmain_id = '" + masterId + "' AND " + lcb + " = '" + enumId
                            + "' AND "+mainPlanStatu+" = '"+mainPlanStatuYwc+"'"+
                            "   UNION ALL " +
                            "   SELECT COUNT(1) AS ywc FROM " + childPlanTableName +
                            "   WHERE formmain_id = '" + masterId + "' AND " + childPlanOwnerMilestone + " = '" + enumId+ "' AND "+ltcLTCPlanStatus+" = '"+mainPlanStatuYwc+"'"+
                            " ) AS subquery";
        } else {

            ywcsql =
                    "SELECT SUM(ywc) AS total_ywc FROM (" +
                            "   SELECT COUNT(1) AS ywc FROM " + zjhTableName +
                            "   WHERE formmain_id = '" + masterId + "' AND " + lcb + " = '" + enumId   + "' AND "+mainPlanStatu+" = '"+mainPlanStatuYwc+"'"+
                            "   UNION ALL " +
                            "   SELECT COUNT(1) AS ywc FROM " + childPlanTableName +
                            "   WHERE formmain_id = '" + masterId + "' AND " + childPlanOwnerMilestone + " = '" + enumId + "' AND "+ltcLTCPlanStatus+" = '"+mainPlanStatuYwc+"'"+
                            "   UNION ALL " +
                            "   SELECT COUNT(1) AS ywc FROM " + ipdTableName +
                            "   WHERE formmain_id = '" + masterId + "' AND " + ipdLTCStage + " = '" + stageItemId +"'"+
                            "   AND " + ipdLTCStatu + " = '" + ipdPlanStatuYwc +"'"+
                            " ) AS subquery";
        }

// 执行查询
        List<Map<String, Object>> res = capDataListSqlHelpManager.selectDataList(ywcsql, null);

        if (!res.isEmpty()) {
            int totalAll =  (int)res.get(0).get("total_ywc");
            numerator = totalAll;
        } else {
            numerator = 0; // 如果没有结果，默认为0
        }



        String combinedAllSumSql ="";
        if (!"ltc_atbps".equals(enumItemCode)) {
            // 不计算 ipdTableName 表中的数量
             combinedAllSumSql =
                    "SELECT SUM(ywc) AS total_ywc FROM (" +
                            "   SELECT COUNT(1) AS ywc FROM " + zjhTableName +
                            "   WHERE formmain_id = '" + masterId + "' AND " + lcb + " = '" + enumId +"'"+
                            "   UNION ALL " +
                            "   SELECT COUNT(1) AS ywc FROM " + childPlanTableName +
                            "   WHERE formmain_id = '" + masterId + "' AND " + childPlanOwnerMilestone + " = '" + enumId +"'"+
                            ") AS subquery";

        } else {
             combinedAllSumSql =
                    "SELECT SUM(ywc) AS total_ywc FROM (" +
                            "   SELECT COUNT(1) AS ywc FROM " + zjhTableName +
                            "   WHERE formmain_id = '" + masterId + "' AND " + lcb + " = '" + enumId +"'"+
                            "   UNION ALL " +
                            "   SELECT COUNT(1) AS ywc FROM " + childPlanTableName +
                            "   WHERE formmain_id = '" + masterId + "' AND " + childPlanOwnerMilestone + " = '" + enumId +"'"+
                            "   UNION ALL " +
                            "   SELECT COUNT(1) AS ywc FROM " + ipdTableName +
                            "   WHERE formmain_id = '" + masterId + "' AND " + ipdLTCStage + " = '" + stageItemId +"'"+
                            " ) AS subquery";
        }
        // 执行查询
        List<Map<String, Object>> allResults = capDataListSqlHelpManager.selectDataList(combinedAllSumSql, null);

        if (!allResults.isEmpty()) {
            int totalAll = (int) allResults.get(0).get("total_ywc");
            denominator = totalAll;
        } else {
            denominator = 1; // 如果没有结果，默认为1
        }

        double bfb=numerator*100.00/denominator;
        // 创建DecimalFormat对象，指定格式为两位小数
        DecimalFormat df = new DecimalFormat("0.00");
        resultDataMap.put(returnName + "Percent",df.format(bfb) );
        }



    /**
     * 根据父枚举代码创建排序数组
     *
     * @param yfxmdaLcbmfjl 父枚举代码，用于获取枚举项
     * @return 返回一个HashMap，键为字符串，值为对象，用于存储排序信息
     * @throws Exception 当获取枚举项或创建排序数组过程中发生错误时抛出异常
     */
    private HashMap<String, Integer> createSortArry(String yfxmdaLcbmfjl) throws Exception {
        // 创建一个HashMap用于存储排序信息
        HashMap<String, Integer> map = new HashMap<>();

        // 通过父枚举代码获取枚举项
        CtpEnumItem lxpjEum = enumManager.getItemByCode(yfxmdaLcbmfjl);
        // 获取该枚举项的所有子枚举项
        List<CtpEnumItem> childEnumItems = enumManager.getChildEnumItems(lxpjEum.getId());

        // 获取子枚举项的数量
        int size = childEnumItems.size();
        // 遍历所有子枚举项，包括0索引处的项
        for(int i=0; i<=size; i++){
            // 将子枚举项的ID作为键，值初始化为0，存入map中
            map.put(StrKit.str(childEnumItems.get(i).getShowvalue()), 0);
        }

        // 返回填充了排序信息的map
        return map;
    }


    /**
     * 五个一
     * @return
     */
    @Override
    public List<Map<String, Object>> getLtcFiveofOne() throws BusinessException, SQLException, ParseException {
        List<Map<String,Object>> resultList = new ArrayList<>();
        Map<String,Object> map=new HashMap<>();
        //立项评审
        map.put("lxps",getProjectReview());
        //合同评审
        map.put("htps",getContractReview());
        //预销售核销
        map.put("yxshx",getPreSaleWriteOff());
        //标准PO
        map.put("bzpo",getStandardPoDo());
        //销区下单
        map.put("xqxd",getPlaceAnOrder());
        resultList.add(map);
        return resultList;
    }

    @Override
    /**
     * 根据父枚举代码获取查询枚举列表
     *
     * @param parentEnumCode 父枚举代码
     * @return 包含子枚举显示值和值的列表
     * @throws Exception 如果发生异常，则抛出异常
     */
    public List<Map<String, Object>> getQueryEnumList(String parentEnumCode) throws  Exception{
        // 初始化结果列表
        List<Map<String, Object>> res=new ArrayList<>();

        // 初始化用于存储枚举显示值和值的映射
        HashMap<String, Object> projectStarLevel = new HashMap<String, Object>();

        // 如果传入的父枚举代码为空，则直接返回null
        if(parentEnumCode==""){
            return  null;
        }

        // 通过父枚举代码获取枚举项
        CtpEnumItem lxpjEum = FormCap4Kit.getEnumByCode(parentEnumCode);
        

        // 如果找到了对应的父枚举项
        if (!ObjectUtils.isEmpty(lxpjEum)) {
            // 查询父枚举项的所有子枚举项
            List<CtpEnumItem> childEnumItems = enumManager.getChildEnumItems(lxpjEum.getId());

            // 遍历子枚举项列表
            for (CtpEnumItem enumItem : childEnumItems) {
                // 将子枚举项的显示值和值添加到映射中
                projectStarLevel.put(enumItem.getShowvalue(), enumItem.getValue());

                // 将映射添加到结果列表
                res.add(projectStarLevel);
            }
        }

        // 返回结果列表
        return res;
    }


    @Override
    public List<Map<String, Object>> getQueryOrgOrDepList(String parm) throws Exception {
        if (parm==""){
            return null;
        }
        List<Map<String, Object>> res = findDepOrPerson(parm);
        return res;
    }

  @Override
public List<Map<String, Object>> getQueryParms() throws Exception {
    // 初始化结果列表
List<Map<String, Object>> res = new ArrayList<>();
Map<String, Object> lmap = new HashMap<>();
Map<String, Object> pmap = new HashMap<>();
Map<String, Object> cmap = new HashMap<>();
Map<String, Object> umap = new HashMap<>();
Set<Map<String, Object>> levelSet = new HashSet<>();
Set<Map<String, Object>> proManagerNameSet = new HashSet<>();
Set<Map<String, Object>> cusManagerNameSet = new HashSet<>();
Set<Map<String, Object>> unitSet = new HashSet<>();

try {
    String fromCode = StrKit.str(AppContext.getSystemProperty("ltc.yfxmda_formCode"));
    FormBean formBean = cap4FormManager.getFormByFormCode(fromCode);
    String level = formBean.getFieldBeanByDisplay("项目星级").getName();
    String tableName = formBean.getFieldBeanByDisplay("项目星级").getOwnerTableName();
    String proManagerName = formBean.getFieldBeanByDisplay("项目经理").getName();
    String cusManagerName = formBean.getFieldBeanByDisplay("客户经理").getName();
    String unit = formBean.getFieldBeanByDisplay("所属销售部门").getName();

    // 拼接sql
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT DISTINCT ").append(level).append(" , ").append(proManagerName).append(" , ").append(cusManagerName).append(" , ").append(unit).append(" FROM ").append(tableName);
    log.info("销售项目信息查询条件sql: " + sql.toString());
    List<Map<String, Object>> masterData = capDataListSqlHelpManager.selectDataList(sql.toString(), null);

    // 处理数据
    if (masterData != null && !masterData.isEmpty()) {
        for (Map<String, Object> map : masterData) {
            String levelValue = StrKit.str(map.get(level));
            String proManagerNameValue = StrKit.str(map.get(proManagerName));
            String cusManagerNameValue = StrKit.str(map.get(cusManagerName));
            String unitValue = StrKit.str(map.get(unit));

            if (!levelValue.isEmpty()) {
                Map<String, Object> levelMap = new HashMap<>();
                levelMap.put(enumManagerNew.getEnumItem(Long.parseLong(levelValue)).getShowvalue(), levelValue);
                levelSet.add(levelMap);
            }

            if (!proManagerNameValue.isEmpty()) {
                Map<String, Object> proManagerMap = new HashMap<>();
                proManagerMap.put(orgManager.getMemberById(Long.parseLong(proManagerNameValue)).getName(), proManagerNameValue);
                proManagerNameSet.add(proManagerMap);
            }

            if (!cusManagerNameValue.isEmpty()) {
                Map<String, Object> cusManagerMap = new HashMap<>();
                cusManagerMap.put(orgManager.getMemberById(Long.parseLong(cusManagerNameValue)).getName(), cusManagerNameValue);
                cusManagerNameSet.add(cusManagerMap);
            }

            if (!unitValue.isEmpty()) {
                Map<String, Object> unitMap = new HashMap<>();
                unitMap.put(orgManager.getDepartmentById(Long.parseLong(unitValue)).getName(), unitValue);
                unitSet.add(unitMap);
            }
        }

        // 将 Set 转换为 List
        lmap.put("levelList", new ArrayList<>(levelSet));
        pmap.put("proManagerNameList", new ArrayList<>(proManagerNameSet));
        cmap.put("cusManagerNameList", new ArrayList<>(cusManagerNameSet));
        umap.put("unitList", new ArrayList<>(unitSet));

        // 确保在处理完数据后将 Map 对象添加到 res 列表中
        res.add(lmap);
        res.add(pmap);
        res.add(cmap);
        res.add(umap);
    }
} catch (Exception e) {
    log.error("Error in getQueryParms", e);
    throw e; // 重新抛出异常以便于上层处理
}

return res;

}


    @Override
    public List<Map<String, Object>> getWarningCenterList() {

        // 初始化结果列表
        List<Map<String, Object>> res = new ArrayList<>();
        // 获取预警中心需要的字段
        String projectName="";
        String doucumentType="";
        String progress="";
        String wholeProgress="";
        String outLimit="";
        String approachLimit="";
        String phaseOutLimit="";
        String phaseApproachLimit="";
        String masterTableName="";
        String devStatu="";
        String planStatu="";
        String subTableName="";
        String lqNum="";
        String lqSubNum="";
        String tableName="";
        String xmqdCount="";
        String planData="";
        String trueData="";
        String sjc="";
        try {
            // 获取系统属性中定义的表代码
            //LTC开发项目档案
            String fromCode = StrKit.str(AppContext.getSystemProperty("ltc.yfxmda_formCode"));
            //String ltc_yjzx = StrKit.str(AppContext.getSystemProperty("ltc.ltc_yjzx"));
            //策划报告档案表
            FormBean xmchFormBean = cap4FormManager.getFormByFormCode("XMCH_001");
            // 根据表代码获取表单对象
            FormBean formBean = cap4FormManager.getFormByFormCode(fromCode);
            masterTableName=formBean.getFieldBeanByDisplay("销售项目名称").getOwnerTableName();
            projectName=formBean.getFieldBeanByDisplay("销售项目名称").getName();
            devStatu=formBean.getFieldBeanByDisplay("LTC开发状态").getName();
            doucumentType=formBean.getFieldBeanByDisplay("单据类型").getName();
            progress=formBean.getFieldBeanByDisplay("LTC主计划阶段").getName();
            //outLimit=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_cqsj")).getName();
            //approachLimit=formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_lqsj")).getName();
            phaseOutLimit=formBean.getFieldBeanByDisplay("超期时间-明细表").getName();
            phaseApproachLimit=formBean.getFieldBeanByDisplay("临期时间-明细表").getName();
            planStatu=formBean.getFieldBeanByDisplay("实际计划状态").getName();
            //lqNum=formBean.getFieldBeanByDisplay("临期时间个数").getName();
            //lqSubNum=formBean.getFieldBeanByDisplay("LTC主计划实际完成时间").getName();
            planData=formBean.getFieldBeanByDisplay("LTC计划结束时间").getName();
            sjc=formBean.getFieldBeanByDisplay("系统时间和预计结束时间差").getName();
            trueData=formBean.getFieldBeanByDisplay("LTC主计划实际完成时间").getName();
            subTableName=formBean.getFieldBeanByDisplay("实际计划状态").getOwnerTableName();
            /*处理枚举*/
            CtpEnumItem kfzt_jxz = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_fieldCode_ltckfztqx"));
            //计划状态 已完成
            CtpEnumItem jhzt_ywc = FormCap4Kit.getEnumByCode("JHZT_YWC");
            //计划状态 关闭取消
            CtpEnumItem jhzt_qx = FormCap4Kit.getEnumByCode("JHZT_QXGB");

            CtpEnumItem djlx_ltc = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_djlx_ltc"));
            /*ltc阶段*/
            CtpEnumItem xmqd =FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_xmjd_xmqd"));
            CtpEnumItem bqyd = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_xmjd_bqyd"));
            CtpEnumItem jjfa = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_xmjd_zdhtjjjfa"));
            CtpEnumItem hrjd = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_xmjd_httpyjd"));
            CtpEnumItem gbxm = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_xmjd_xsxmgb"));

            CtpEnumItem sf_f = FormCap4Kit.getEnumByCode("N");
            String isJsch=xmchFormBean.getFieldBeanByDisplay("策划报告是否及时").getName();
            String isJszjh=xmchFormBean.getFieldBeanByDisplay("主计划制定是否及时").getName();
            String isJskgh=xmchFormBean.getFieldBeanByDisplay("开工会是否及时").getName();
            tableName=xmchFormBean.getFieldBeanByDisplay("开工会是否及时").getOwnerTableName();

            String zjh=xmchFormBean.getFieldBeanByDisplay("主计划编制完成时间").getName();
            String cgsj=xmchFormBean.getFieldBeanByDisplay("策划报告草稿时间").getName();
            String kgh=xmchFormBean.getFieldBeanByDisplay("开工会后时间").getName();
            //项目当前阶段
            String xmqddqjd=xmchFormBean.getFieldBeanByDisplay("项目当前阶段").getName();
            //项目当前阶段
            String chbgxmzt=xmchFormBean.getFieldBeanByDisplay("项目状态").getName();
            //机会阶段 项目启动
            CtpEnumItem lxjc = FormCap4Kit.getEnumByCode("LXJC");
            //项目状态 进行中
            CtpEnumItem xmzt_jxz = FormCap4Kit.getEnumByCode("XMZT_JXZ");

            /*处理每个阶段的枚举*/
            // 构建查询 SQL
            StringBuilder queryPhaseProgressSql = new StringBuilder();

            queryPhaseProgressSql.append("SELECT ")
                    .append("COUNT(1)")
                    .append("FROM ").append(masterTableName);


            log.info("ltc预警中心主表查询sql"+queryPhaseProgressSql.toString());
            List masterData = capDataListSqlHelpManager.selectDataList(queryPhaseProgressSql.toString(), null);
            log.info("ltc预警中心主表查询sql结果"+masterData.size());

            /**项目启动逻辑
             * 项目状态=进行中 and 项目当前阶段=机会阶段.项目启动
             * and（ （策划是否及时=否 且 不为空）and（主计划是否及时=否 且 不为空）and（开工会是否及时=否 且 不为空））
             * */
            //构建预警中心项目启动超期数量查询sql
            //String xmqdSql="SELECT COUNT(*) as count FROM "+tableName+" WHERE "+isJsch+" = "+sf_f.getId()+" OR "+isJszjh+" = "+sf_f.getId()+" OR "+isJskgh+" = "+sf_f.getId();
            String xmqdSql="SELECT COUNT(*) as count FROM "+tableName+" WHERE " +xmqddqjd+" = "+lxjc.getId()+" and " +
                    chbgxmzt+" = "+xmzt_jxz.getId()+
                    " and ("+
                    "( "+isJsch+" = "+sf_f.getId()+" and "+cgsj+" is not null) " +
                    "OR ( "+isJszjh+" = "+sf_f.getId()+" and "+zjh+" is not null) " +
                    "OR ( "+isJskgh+" = "+sf_f.getId()+" and "+kgh+" is not null )"+" )";
            List xmqdData = capDataListSqlHelpManager.selectDataList(xmqdSql, null);
            if(!xmqdData.isEmpty()){
                Map<String,Object> map=(Map)xmqdData.get(0);
                xmqdCount = StrKit.str(map.get("count"));
            }

            if (masterData.size()>0){
                    HashMap<String, Object> data = new HashMap<>();
                    Map<String,Object> map=(Map)masterData.get(0);
                    String formRecordid = StrKit.str(map.get("id"));
                    data.put("OUTLIMITSUM",StrKit.str(map.get("out_limit_sum")));
                    data.put("APPROACHLIMITSUM",StrKit.str(map.get("approach_limit_sum")));
                    data.put("PROJECTNAME",StrKit.str(map.get("project_name")));
                    data.put("cqurl",FormCap4Kit.getBbAddres("LTC项目-超期数量"));
                    data.put("lqurl",FormCap4Kit.getBbAddres("LTC项目-临期数量"));

                    /*五个里程碑 项目启动 标前引导 制定解决方案 合同签订雨谈判 销售项目关闭*/

                    //项目启动
                    data.put("xmqdOUTLIMITSUM",xmqdCount);
                    data.put("xmqdAPPROACHLIMITSUM","0");
                    data.put("xmqdcqUrl", FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.xmqdcqurl"))));
                    data.put("xmqdlqUrl",FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.xmqdlqurl"))));

                    /*查询组装子阶段的数据*/
                    //List xmqdList = querySubPhaseData(phaseOutLimit, phaseApproachLimit, subTableName, planStatu, jhzt_ywc,jhzt_qx, formRecordid, xmqd, progress,lqSubNum);
                    /*处理数据*/
                    //dealQueryData(xmqdList,data,res,new String("xmqd"));
                    List bqydList = querySubPhaseData(trueData,planData,subTableName,planStatu,jhzt_ywc,jhzt_qx,bqyd,progress,sjc);
                    dealQueryData(bqydList,data,res,new String("bqyd"));
                    List jjfaList = querySubPhaseData(trueData,planData,subTableName,planStatu,jhzt_ywc,jhzt_qx,jjfa,progress,sjc);
                    dealQueryData(jjfaList,data,res,new String("jjfa"));
                    List hrjdList = querySubPhaseData(trueData,planData,subTableName,planStatu,jhzt_ywc,jhzt_qx,hrjd,progress,sjc);
                    dealQueryData(hrjdList,data,res,new String("hrjd"));
                    List gbxmList = querySubPhaseData(trueData,planData,subTableName,planStatu,jhzt_ywc,jhzt_qx,gbxm,progress,sjc);
                    dealQueryData(gbxmList,data,res,new String("gbxm"));
                    res.add(data);


            }



        }catch (Exception e){

      }


        return res;
    }

    @Override
    /**
     * 根据资源类型获取资源池
     *
     * @param rosourceTyped 资源类型
     * @return 返回一个列表，列表中每个元素是一个映射，键为字符串，值为对象
     * @throws Exception 如果发生异常，则抛出异常
     */
    public List<Map<String, Object>> getResourcePool(String resourceType) throws Exception {
    // 初始化结果列表
    List<Map<String, Object>> res = new ArrayList<>();

    // 如果资源类型为空字符串，则返回null
    if (resourceType == "") {
        return null;
    }

    // 根据资源类型获取资源池数据
    List<Map<String, Object>> masterData = getResourcePoolData(resourceType);

    // 如果获取的数据不为空，则进行数据聚合
    if (!masterData.isEmpty()) {
        // 初始化聚合数据结构，用于按管理者ID和等级聚合数据
        Map<String, Map<String, Integer>> aggregatedData = new HashMap<>();

        // 遍历原始数据，进行数据聚合
        for (Map<String, Object> dataMap : masterData) {
            String managerId = StrKit.str(dataMap.get("manager"));
            String levelId = StrKit.str(dataMap.get("level"));
            String levelNumStr = StrKit.str(dataMap.get("all_projects"));
            String allProjectsStr = StrKit.str(dataMap.get("total_projects"));

            int levelNum = Integer.parseInt(levelNumStr);
            int allProjects = Integer.parseInt(allProjectsStr);

            String level = enumManagerNew.getEnumItem(Long.parseLong(levelId)).getShowvalue();

            // 初始化或更新聚合数据
            Map<String, Integer> managerData = aggregatedData.getOrDefault(managerId, new HashMap<>());
            managerData.merge(level, levelNum, Integer::sum);
            managerData.put("ALL_PROJECTS", allProjects);
            aggregatedData.put(managerId, managerData);
        }

        // 收集所有 ALL_PROJECTS 的值
        List<Integer> allProjectsValues = new ArrayList<>();
        for (Map.Entry<String, Map<String, Integer>> entry : aggregatedData.entrySet()) {
            allProjectsValues.add(entry.getValue().getOrDefault("ALL_PROJECTS", 0));
        }
        Collections.sort(allProjectsValues, Collections.reverseOrder()); // 降序排列

        // 为每个结果计算 SORT 值
        Map<Integer, Integer> sortMap = new HashMap<>();
        for (int i = 0; i < allProjectsValues.size(); i++) {
            sortMap.put(allProjectsValues.get(i), i + 1);
        }

        // 遍历聚合数据，构造最终结果
        for (Map.Entry<String, Map<String, Integer>> entry : aggregatedData.entrySet()) {
            String managerId = entry.getKey();
            Map<String, Integer> levelCounts = entry.getValue();

            // 初始化结果映射
            Map<String, Object> resultMap = new HashMap<>();
            V3xOrgMember member = orgManager.getMemberById(Long.parseLong(managerId));
            resultMap.put("MANAGER", member.getName());

            // 添加每个等级的数量
            for (Map.Entry<String, Integer> levelEntry : levelCounts.entrySet()) {
                String level = levelEntry.getKey();
                int count = levelEntry.getValue();
                resultMap.put(level, count);
            }

            // 添加总项目数量，并计算 SORT 值
            int allProjects = levelCounts.getOrDefault("ALL_PROJECTS", 0);
            resultMap.put("ALL_PROJECTS", allProjects);
            resultMap.put("SORT", sortMap.getOrDefault(allProjects, 0)); // 使用默认值 0

            // 添加 URL
            if ("projectManager".equals(resourceType)) {
                resultMap.put("url", FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.zycprojectjl"))));
            } else if ("cusManager".equals(resourceType)) {
                resultMap.put("url", FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.zyccusjl"))));
            } else if ("productManager".equals(resourceType)) {
                resultMap.put("url", FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.zycproducejl"))));
            } else if ("deliveryManager".equals(resourceType)) {
                resultMap.put("url", FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.zycdeljl"))));
            }

            // 将结果映射添加到结果列表
            res.add(resultMap);
        }
    }

    // 如果获取的数据为空，则返回null
    return res;
}


    private List getResourcePoolData(String rosourceTyped) {
        String projectManager = "";
        String cusManager = "";
        String productManager = "";
        String deliveryManager = "";
        String devStatu = "";
        String level = "";
        String masterTableName = "";
        String doucumentType = "";
        StringBuilder querySql = new StringBuilder();
        try {
            String formCode = AppContext.getSystemProperty("ltc.yfxmda_formCode");
            FormBean formBean = cap4FormManager.getFormByFormCode(formCode);

            // 获取表名
             masterTableName = formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_xsxmmc")).getOwnerTableName();

            // 获取开发状态
             devStatu = formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_ltckfzt")).getName();

            // 获取档案类型
             doucumentType = formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_pjxq")).getName();

            // 根据 rosourceTyped 获取相应的角色字段
            String managerField = "";
            switch (rosourceTyped) {
                case "projectManager":
                    managerField = formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_xmjl")).getName();
                    break;
                case "cusManager":
                    managerField = formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_khjl")).getName();
                    break;
                case "productManager":
                    managerField = formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_cpjl")).getName();
                    break;
                case "deliveryManager":
                    managerField = formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_jfjl")).getName();
                    break;
                default:
                    throw new IllegalArgumentException("Invalid rosourceTyped: " + rosourceTyped);
            }

            // 获取项目星级字段
            String levelField = formBean.getFieldBeanByDisplay(AppContext.getSystemProperty("ltc.ltc_fieldCode_xqpj")).getName();

            // 获取项目星级枚举
            Long doubleTenA = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_enum_ssa")).getId();
            Long doubleTenB = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_enum_ssb")).getId();
            Long lxpja = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_enum_lxa")).getId();
            Long lxpjb = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_enum_lxb")).getId();
            Long lxpjc = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_enum_lxc")).getId();
            Long lxpjd = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_enum_lxd")).getId();

            // 获取开发状态枚举 取消关闭
            //Long ltc_kfzt_qx = FormCap4Kit.getEnumByCode("ltcfkztqx").getId();
            //ltc开发状态 进行中
            Long ltcfkztjxz = FormCap4Kit.getEnumByCode("ltcfkztjxz").getId();

            // 获取单据类型 ltc
            Long djlx_ltc = FormCap4Kit.getEnumByCode(AppContext.getSystemProperty("ltc.ltc_djlx_ltc")).getId();

            // 构建 SQL 查询语句
            // 构建 SQL 查询语句
            /*querySql.setLength(0); // 清空 StringBuilder
            querySql.append("SELECT t.manager, t.total_projects, t.all_projects, t.level FROM (");
            querySql.append("SELECT ").append(managerField).append(" AS manager, ");
            querySql.append("(SELECT COUNT(*) FROM ").append(masterTableName).append(" f WHERE f.").append(managerField).append(" = m.").append(managerField).append(" AND f.").append(levelField).append(" IS NOT NULL) AS total_projects, ");
            querySql.append("COUNT(*) AS all_projects, ");
            querySql.append(levelField).append(" AS level FROM ").append(masterTableName).append(" m ");
            querySql.append("WHERE ").append(levelField).append(" IN (").append(doubleTenA).append(", ").append(doubleTenB).append(", ").append(lxpja).append(", ").append(lxpjb).append(", ").append(lxpjc).append(", ").append(lxpjd).append(") ");
            querySql.append("AND ").append(devStatu).append(" != ").append(ltc_kfzt_qx).append(" ");
            querySql.append("AND ").append(doucumentType).append(" = ").append(djlx_ltc).append(" ");
            querySql.append("GROUP BY ").append(managerField).append(", ").append(levelField).append(" ) t ");
            querySql.append("ORDER BY t.total_projects DESC LIMIT 10");*/

            querySql.setLength(0); // 清空 StringBuilder
            querySql.append("SELECT TOP 10 ");
            querySql.append("t.manager, t.total_projects, t.all_projects, t.level FROM (");
            querySql.append("SELECT ").append(managerField).append(" AS manager, ");
            querySql.append("(SELECT COUNT(*) FROM ").append(masterTableName).append(" f WHERE f.").append(managerField).append(" = m.").append(managerField).append(" AND f.").append(levelField).append(" IS NOT NULL) AS total_projects, ");
            querySql.append("COUNT(*) AS all_projects, ");
            querySql.append(levelField).append(" AS level FROM ").append(masterTableName).append(" m ");
            querySql.append("WHERE ").append(levelField).append(" IN (").append(doubleTenA).append(", ").append(doubleTenB).append(", ").append(lxpja).append(", ").append(lxpjb).append(", ").append(lxpjc).append(", ").append(lxpjd).append(") ");
            querySql.append("AND ").append(devStatu).append(" = ").append(ltcfkztjxz).append(" ");
            querySql.append("AND ").append(doucumentType).append(" = ").append(djlx_ltc).append(" ");
            querySql.append("GROUP BY ").append(managerField).append(", ").append(levelField).append(" ) t ");
            querySql.append("ORDER BY t.total_projects DESC");



            log.info("预警资源池sql: " + querySql.toString());
            List list = capDataListSqlHelpManager.selectDataList(querySql.toString(), null);
            return list;
        } catch (Exception e) {
            log.info("预警资源池sql查询方法报错：getResourcePoolData", e);
        }
        return null;
    }

    /**
     * 项目成功率
     * @return
     */
    @Override
    public Map<String, Object> getProjectInfoDataList() {
        DecimalFormat df = new DecimalFormat("0.00");
        /* 初始化结果映射表 */
        Map<String, Object> resultMap = new HashMap<String, Object>();
        try {
            /* 从系统属性中获取配置信息 */
            String xmdaFormCode = AppContext.getSystemProperty("kanban.xmdaFormCode");
            String projectLevel = AppContext.getSystemProperty("kanban.projectLevel");
            String projectStatus = AppContext.getSystemProperty("kanban.projectStatus");
            String currentStage = AppContext.getSystemProperty("kanban.currentStage");
            String projectName = AppContext.getSystemProperty("kanban.projectName");
            String xmda_zbje=AppContext.getSystemProperty("ltc.xmda_zbje");
            String xmda_xmcgl=AppContext.getSystemProperty("ltc.xmda_xmcgl");
            String xmda_xmbm=AppContext.getSystemProperty("ltc.xmda_xmbm");
            String sfzb = AppContext.getSystemProperty("kanban.sfzb");
            String ydsj="ydsj";

            /* 根据表单代码获取表单bean */
            FormBean formBean = cap4FormManager.getFormByFormCode(xmdaFormCode);
            List<FormFieldBean> formFieldBeans = formBean.getMasterTableBean().getFields();
            StringBuilder stb = new StringBuilder();
            stb.append("select id, ");
            String xmxjFieldName = "";
            String xmztFieldName = "";
            String dqjdFieldName = "";
            String xmmcFieldName = "";
            String sfzbFieldName = "";
            String xmcglFieldName = "";
            String xmbmFieldName = "";
            String zbjeFieldName = "";
            String ydsjFieldName = "";
            /* 遍历表单字段，构建SQL查询语句，并获取相关字段名称 */
            for (FormFieldBean formFieldBean : formFieldBeans) {
                String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
                if (xmda_xmcgl.equals(fieldDesc)) {
                    xmcglFieldName = formFieldBean.getName();
                }
                if (xmda_xmbm.equals(fieldDesc)){
                    xmbmFieldName = formFieldBean.getName();
                }
                if (projectLevel.equals(fieldDesc)) {
                    xmxjFieldName = formFieldBean.getName();
                }
                if (projectStatus.equals(fieldDesc)) {
                    xmztFieldName = formFieldBean.getName();
                }
                if (currentStage.equals(fieldDesc)) {
                    dqjdFieldName = formFieldBean.getName();
                }
                if (projectName.equals(fieldDesc)) {
                    xmmcFieldName = formFieldBean.getName();
                }
                if (sfzb.equals(fieldDesc)) {
                    sfzbFieldName = formFieldBean.getName();
                }
                if (xmda_zbje.equals(fieldDesc)){
                    zbjeFieldName = formFieldBean.getName();
                }
                if (ydsj.equals(fieldDesc)){
                    ydsjFieldName = formFieldBean.getName();
                }
                if (!"".equals(fieldDesc)) {
                    String fieldName = formFieldBean.getName();
                    stb.append(fieldName).append(",");
                }
            }
            /* 删除最后一个逗号，完成SQL查询语句的构建 */
            String str = stb.substring(0, stb.length() - 1);
            String tableName = formBean.getMasterTableBean().getTableName();
            String newStr = str + " from " + tableName;
            log.info("中标率sql语句：" + newStr);
            /* 执行SQL查询 */
            List dataList = capDataListSqlHelpManager.selectDataList(newStr, null);
            /* 处理查询结果，统计各类项目的数量 */
            if (!dataList.isEmpty()) {
                List<Map<String, Object>> ssDataList = new ArrayList<Map<String, Object>>();
                List<Map<String, Object>> fssDataList = new ArrayList<Map<String, Object>>();
                int ssydNum = 0;
                int ssgzNum = 0;
                int ssjxzNum = 0;
                int fssydNum = 0;
                int fssgzNum = 0;
                int fssjxzNum = 0;
                int sssdNum = 0;
                int fsssdNum = 0;
                double ssxmgcl= 0.0;
                int ssHaveXmgclNum=0;
                double sszbje=0.0;
                double fsszbje=0.0;
                int fssydCount=0;
                List<String> ssIds=new ArrayList<>();
                List<String> fssIds=new ArrayList<>();
                for (int i = 0; i < dataList.size(); i++) {
                    Map<String, Object> dataMap = (Map<String, Object>) dataList.get(i);
                    Map<String, Object> tempDataMap = new HashMap<String, Object>();
                    String id = StrKit.str(dataMap.get("id"));
                    String url = "/seeyon/cap4/businessTemplateController.do?method=formContent&type=browse&rightId=-6498463788701760512.-7013542437671124890&moduleId=" + id + "&moduleType=42";
                    tempDataMap.put("url", url);
                    tempDataMap.put("projectName", dataMap.get(xmmcFieldName));
                    String xmxj = StrKit.str(dataMap.get(xmxjFieldName));
                    String xmxjEnumItemCode = "";
                    /* 根据项目星级获取枚举项代码和显示值 */
                    if (!"".equals(xmxj)) {
                        CtpEnumItem ctpEnumItem = enumManager.getCtpEnumItem(Long.parseLong(xmxj));
                        xmxjEnumItemCode = ctpEnumItem.getEnumItemCode();
                        tempDataMap.put("projectLevel", ctpEnumItem.getShowvalue());
                    } else {
                        tempDataMap.put("projectLevel", "");
                    }
                    String sfzbValue = StrKit.str(dataMap.get(sfzbFieldName));
                    CtpEnumItem sfzbEnumItem = null;
                    if (!"".equals(sfzbValue)) {
                        sfzbEnumItem = enumManager.getCtpEnumItem(Long.parseLong(sfzbValue));
                    }
                    String xmzt = StrKit.str(dataMap.get(xmztFieldName));
                    String xmztEnumItemCode = "";
                    /* 根据项目状态获取枚举项代码和显示值 */
                    if (!"".equals(xmzt)) {
                        CtpEnumItem ctpEnumItem = enumManager.getCtpEnumItem(Long.parseLong(xmzt));
                        xmztEnumItemCode = ctpEnumItem.getEnumItemCode();
                        tempDataMap.put("projectStatus", ctpEnumItem.getShowvalue());
                    } else {
                        tempDataMap.put("projectStatus", "");
                    }
                    String dqjd = StrKit.str(dataMap.get(dqjdFieldName));
                    String dqjdEnumItemCode = "";
                    /* 根据当前阶段获取枚举项代码和显示值 */
                    if (!"".equals(dqjd)) {
                        CtpEnumItem ctpEnumItem = enumManager.getCtpEnumItem(Long.parseLong(dqjd));
                        dqjdEnumItemCode = ctpEnumItem.getEnumItemCode();
                        tempDataMap.put("dqjd", ctpEnumItem.getShowvalue());
                    } else {
                        tempDataMap.put("dqjd", "");
                    }

                    String sfzbShowValue = "";
                    if (sfzbEnumItem != null) {
                        sfzbShowValue = sfzbEnumItem.getShowvalue();
                    }
                    /* 根据项目类型（双十项目或非双十项目）进行数据分类统计 */
                    if ("lx4_3".equals(xmxjEnumItemCode) || "lx5_3".equals(xmxjEnumItemCode)) {
                        if (dataMap.get(xmbmFieldName)!=null&&!dataMap.get(xmbmFieldName).toString().isEmpty()){
                            ssIds.add(dataMap.get(xmbmFieldName).toString());
                        }
                        if (dataMap.get(xmcglFieldName)!= null&& !dataMap.get(xmcglFieldName).toString().isEmpty()){
                            ssxmgcl += Double.parseDouble(StrKit.str(dataMap.get(xmcglFieldName)));
                            ssHaveXmgclNum+=1;
                        }
                        ssDataList.add(tempDataMap);
                        if (!"XMZT_QXHFQ".equals(xmztEnumItemCode) && "YD".equals(dqjdEnumItemCode)) {
                            if (dataMap.get(zbjeFieldName)!=null&&!dataMap.get(zbjeFieldName).toString().isEmpty()){
                                sszbje+=Double.parseDouble(StrKit.str(dataMap.get(zbjeFieldName)));
                            }
                            ssydNum += 1;
                        }
                        if (!"XMZT_QXHFQ".equals(xmztEnumItemCode) && "SD".equals(dqjdEnumItemCode)) {
                            sssdNum += 1;
                        }
                        if ("XMZT_QXHFQ".equals(xmztEnumItemCode)) {
                            ssgzNum += 1;
                        }
                        if ("XMZT_JXZ".equals(xmztEnumItemCode)&& (!"YD".equals(dqjdEnumItemCode)&&!"SD".equals(dqjdEnumItemCode)&&!"CJ".equals(dqjdEnumItemCode))) {
                            ssjxzNum += 1;
                        }
                    } else {
                        if (dataMap.get(xmbmFieldName)!=null&&!dataMap.get(xmbmFieldName).toString().isEmpty()){
                            fssIds.add(dataMap.get(xmbmFieldName).toString());
                        }
                        fssDataList.add(tempDataMap);
                        if (!"XMZT_QXHFQ".equals(xmztEnumItemCode) && "YD".equals(dqjdEnumItemCode)) {
                            if (dataMap.get(zbjeFieldName)!=null&&!dataMap.get(zbjeFieldName).toString().isEmpty()){
                                fsszbje+=Double.parseDouble(StrKit.str(dataMap.get(zbjeFieldName)));
                            }
                            fssydCount+= 1;
                            if (dataMap.get(ydsjFieldName)==null||dataMap.get(ydsjFieldName)==""){
                              continue;
                            }else{
                                // 获取当前年份
                                int currentYear = LocalDate.now().getYear();
                                // 从dataMap中获取日期字符串
                                String dateStr = dataMap.get(ydsjFieldName).toString();
                                // 定义日期格式
                                DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd hh:mm:ss.S");
                                // 解析日期字符串为LocalDate对象
                                LocalDate date = LocalDate.parse(dateStr, formatter);
                                // 比较年份
                                boolean isCurrentYear = date.getYear() == currentYear;
                                if (isCurrentYear){
                                    fssydNum += 1;
                                }
                            }
                        }
                        if (!"XMZT_QXHFQ".equals(xmztEnumItemCode) && "SD".equals(dqjdEnumItemCode)) {
                            fsssdNum += 1;
                        }
                        if ("XMZT_QXHFQ".equals(xmztEnumItemCode)) {
                            fssgzNum += 1;
                        }
                        if ("XMZT_JXZ".equals(xmztEnumItemCode)&& (!"YD".equals(dqjdEnumItemCode)&&!"SD".equals(dqjdEnumItemCode)&&!"CJ".equals(dqjdEnumItemCode))) {
                            fssjxzNum += 1;
                        }
                    }
                }


                //双十
                String ssxm_ydsl = AppContext.getSystemProperty("kanban.ssxm_ydsl");
                String ssxm_sdsl = AppContext.getSystemProperty("kanban.ssxm_sdsl");
                String ssxm_gzsl = AppContext.getSystemProperty("kanban.ssxm_gzsl");
                String ssxm_jxsl = AppContext.getSystemProperty("kanban.ssxm_jxsl");
                //非双十
                String fssxm_ydsl = AppContext.getSystemProperty("kanban.fssxm_ydsl");
                String fssxm_sdsl = AppContext.getSystemProperty("kanban.fssxm_sdsl");
                String fssxm_gzsl = AppContext.getSystemProperty("kanban.fssxm_gzsl");
                String fssxm_jxsl = AppContext.getSystemProperty("kanban.fssxm_jxsl");
                /* 计算并构建双十项目和非双十项目的中标率数据 */
                /* 双十数据 */
                Map<String, Object> ssdataMap = new HashMap<String, Object>();
                /* 赢单 */
                Map<String, Object> ssydMap = new HashMap<String, Object>();
                ssydMap.put("num", ssydNum);
                ssydMap.put("url", FormCap4Kit.getBbAddres("LTC-双十项目-赢单数量"));
                ssdataMap.put("yd", ssydMap);
                /* 输单 */
                Map<String, Object> sssdMap = new HashMap<String, Object>();
                sssdMap.put("num", sssdNum);
                sssdMap.put("url", FormCap4Kit.getBbAddres("LTC-双十项目-输单数量 "));
                ssdataMap.put("sd", sssdMap);
                /* 搁置 */
                Map<String, Object> ssgzMap = new HashMap<String, Object>();
                ssgzMap.put("num", ssgzNum);
                ssgzMap.put("url", FormCap4Kit.getBbAddres(ssxm_gzsl));
                ssdataMap.put("gz", ssgzMap);
                /* 进行中 */
                Map<String, Object> ssjxzMap = new HashMap<String, Object>();
                ssjxzMap.put("num", ssjxzNum);
                ssjxzMap.put("url", FormCap4Kit.getBbAddres("LTC-双十项目-进行数量 "));
                ssdataMap.put("jxz", ssjxzMap);
                int ssNum = ssDataList.size();
                Double sszbl=0d;
                if ((ssydNum + sssdNum)==0){
                     sszbl = 0d;
                }else{
                    sszbl = (double) (ssydNum * 100) / ssNum;
                }
                ssdataMap.put("zbl", sszbl);
                ssdataMap.put("xmcgl", getCgl());
                ssdataMap.put("zbje",  df.format(sszbje));
                ssdataMap.put("ssSumOfMoney", getSumOfMoneyBySsOrFss(ssIds,"双十总订单金额",true));
                ssdataMap.put("ssDataList", ssDataList);
                /*双十穿透链接*/
                //中标金额
                ssdataMap.put("sszbjeUrl",FormCap4Kit.getBbAddres("LTC-双十项目-中标金额"));
                //项目成功率
                ssdataMap.put("ssxmcglUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-260149640001073307&formId=22558257697935243&type=baseInfo&tag=1729493671992&recommendMenuId=7936746582482896408&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-2989901594518796315");
                //中标率
                ssdataMap.put("sszblUrl",FormCap4Kit.getBbAddres("LTC-双十项目-中标率"));
                //全年总订单金额
                ssdataMap.put("ssqnjeUrl",FormCap4Kit.getBbAddres("LTC-双十订单总金额（本年）"));


                /* 非双十 */
                Map<String, Object> fssdataMap = new HashMap<String, Object>();
                /* 赢单 */
                Map<String, Object> fssydMap = new HashMap<String, Object>();
                fssydMap.put("num", fssydNum);
                fssydMap.put("url", FormCap4Kit.getBbAddres("LTC-非双十项目-赢单数量"));
                fssdataMap.put("yd", fssydMap);
                /* 输单 */
                Map<String, Object> fsssdMap = new HashMap<String, Object>();
                fsssdMap.put("num", fsssdNum);
                fsssdMap.put("url", FormCap4Kit.getBbAddres("LTC-非双十项目-输单数量"));
                fssdataMap.put("sd", fsssdMap);
                /* 搁置 */
                Map<String, Object> fssgzMap = new HashMap<String, Object>();
                fssgzMap.put("num", fssgzNum);
                fssgzMap.put("url", FormCap4Kit.getBbAddres(fssxm_gzsl));
                fssdataMap.put("gz", fssgzMap);
                Map<String, Object> fssjxzMap = new HashMap<String, Object>();
                fssjxzMap.put("num", fssjxzNum);
                fssjxzMap.put("url", FormCap4Kit.getBbAddres("LTC-非双十项目-进行数量"));
                fssdataMap.put("jxz", fssjxzMap);
                int fssNum = fssDataList.size();
                Double fsszbl = 0d;
                if ((fssydNum+fsssdNum)==0){
                    fsszbl = 0d;
                }else{
                    fsszbl = (double) (fssydCount * 100) / fssNum;
                }
                fssdataMap.put("zbl", fsszbl);
                fssdataMap.put("fssSumOfMoney", getSumOfMoneyBySsOrFss(fssIds,"非双十总订单金额",false));
                fssdataMap.put("fssDataList", fssDataList);
                fssdataMap.put("zbje", df.format(fsszbje));

                /*非双十穿透链接*/
                //中标金额
                fssdataMap.put("fsszbjeUrl",FormCap4Kit.getBbAddres("LTC-非双十项目-中标金额"));
                //项目成功率
                fssdataMap.put("fssxmcglUrl",FormCap4Kit.getBbAddres("LTC-非双十项目-项目成功率"));
                //中标率
                fssdataMap.put("fsszblUrl",FormCap4Kit.getBbAddres("LTC-非双十项目-中标率"));
                //全年总订单金额
                fssdataMap.put("fssqnjeUrl",FormCap4Kit.getBbAddres("LTC-非双十订单总金额（本年）"));

                Map<String, Object> resultDataMap = new HashMap<String, Object>();
                resultDataMap.put("ssdata", ssdataMap);
                resultDataMap.put("fssdata", fssdataMap);
                resultMap.put("data", resultDataMap);
            }
            /* 设置结果状态码和消息 */
            resultMap.put("code", 200);
            resultMap.put("msg", "");

            return resultMap;
        } catch (Exception e) {
            /* 记录错误日志，并返回错误信息 */
            log.error("获取双十项目中标率数据错误:", e);
            resultMap.put("code", 500);
            resultMap.put("msg", e.getMessage());
            resultMap.put("data", "");
            return resultMap;
        }

    }

    @Override
    public Map<String, Object> getContractReviewFirstOne() throws BusinessException, SQLException {
        Map<String, Object> resMap = new HashMap<>();
        // 获取五个一合同评审平均处理时间
        resMap.put("pjclsj",getCalculateTime(AppContext.getSystemProperty("ltc.ltcHtpslcDaTemp"),"合同评审","ltc.ltc_pjsj"));
        resMap.put("sybsj",getCalculateTime(AppContext.getSystemProperty("ltc.ltcHtpslcDaTemp"),"事业部","ltc.ltcHtpslcDa_sybDiffTime"));
        resMap.put("syssj",getCalculateTime(AppContext.getSystemProperty("ltc.ltcHtpslcDaTemp"),"四要素","ltc.ltcHtpslcDa_sysDiffTime"));
        resMap.put("xszjsj",getCalculateTime(AppContext.getSystemProperty("ltc.ltcHtpslcDaTemp"),"销售总监","ltc.ltcHtpslcDa_xszjDiffTime"));
        return resMap;
    }

    @Override
    public List<Map<String, Object>> getContractReviewSecondOne(String type) throws BusinessException, SQLException {
        String ltcHtpslcDaTemp = AppContext.getSystemProperty("ltc.ltcHtpslcDaTemp"); //合同签订流程档案模板编码（档案表）
        String ltcHtpslcDa_sybPerson = AppContext.getSystemProperty("ltc.ltcHtpslcDa_sybPerson"); //事业部负责人
        String ltcHtpslcDa_jfPerson = AppContext.getSystemProperty("ltc.ltcHtpslcDa_jfPerson"); //交付负责人
        String ltcHtpslcDa_fwPerson = AppContext.getSystemProperty("ltc.ltcHtpslcDa_fwPerson"); //法务负责人
        String ltcHtpslcDa_cwPerson=AppContext.getSystemProperty("ltc.ltcHtpslcDa_cwPerson"); //财务负责人
        String ltcHtpslcDa_cpPerson=AppContext.getSystemProperty("ltc.ltcHtpslcDa_cpPerson"); //产品负责人
        String ltcHtpslcDa_xszjPerson=AppContext.getSystemProperty("ltc.ltcHtpslcDa_xszjPerson"); //销售总监负责人

        String ltcHtpslcDaMx_sybDiffTime = AppContext.getSystemProperty("ltc.ltcHtpslcDaMx_sybDiffTime"); //事业部时间差
        String ltcHtpslcDaMx_jfDiffTime = AppContext.getSystemProperty("ltc.ltcHtpslcDaMx_jfDiffTime"); //交付时间差
        String ltcHtpslcDaMx_fwDiffTime = AppContext.getSystemProperty("ltc.ltcHtpslcDaMx_fwDiffTime"); //法务时间差
        String ltcHtpslcDaMx_cwDiffTime=AppContext.getSystemProperty("ltc.ltcHtpslcDaMx_cwDiffTime"); //财务时间差
        String ltcHtpslcDaMx_cpDiffTime=AppContext.getSystemProperty("ltc.ltcHtpslcDaMx_cpDiffTime"); //产品时间差
        String ltcHtpslcDaMx_xszjDiffTime=AppContext.getSystemProperty("ltc.ltcHtpslcDaMx_xszjDiffTime"); //销售总监时间差
        String ltcHtpslcDaMx_Time="ltcHtpslcDaMx_Time"; //发起人-提交时间


        String ltcHtpsDaMx_fqTime="ltcHtpsDaMx_fqTime";

        int thisYear = LocalDate.now().getYear();
        // 设置日期为今年的第一天
        LocalDate startOfYear = LocalDate.of(thisYear, 1, 1);
        //今年最后一天
        LocalDate endOfYear = LocalDate.of(thisYear, 12, 31);

        //根据档案编码获取formBean对象
        FormBean formBean = cap4FormManager.getFormByFormCode(ltcHtpslcDaTemp);
        List<FormFieldBean> formFieldBeans = formBean.getAllFieldBeans();
        StringBuilder stb = new StringBuilder();
        stb.append("select  ");
        String sybPersonFieldName="";
        String jfPersonFieldName="";
        String fwPersonFieldName="";
        String cwPersonFieldName="";
        String cpPersonFieldName="";
        String xszjPersonFieldName="";

        String sybDiffTimeFieldName="";
        String jfDiffTimeFieldName="";
        String fwDiffTimeFieldName="";
        String cwDiffTimeFieldName="";
        String cpDiffTimeFieldName="";
        String xszjDiffTimeFieldName="";
        String TimeFieldName="";
        String fqTimeFieldName="";
        for (FormFieldBean formFieldBean : formFieldBeans) {
            String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
            if (ltcHtpslcDa_sybPerson.equals(fieldDesc)) {
                sybPersonFieldName = formFieldBean.getName();
            }
            if (ltcHtpslcDa_jfPerson.equals(fieldDesc)) {
                jfPersonFieldName = formFieldBean.getName();
            }
            if (ltcHtpslcDa_fwPerson.equals(fieldDesc)) {
                fwPersonFieldName = formFieldBean.getName();
            }
            if (ltcHtpslcDa_cwPerson.equals(fieldDesc)) {
                cwPersonFieldName = formFieldBean.getName();
            }
            if (ltcHtpslcDa_cpPerson.equals(fieldDesc)) {
                cpPersonFieldName = formFieldBean.getName();
            }
            if (ltcHtpslcDa_xszjPerson.equals(fieldDesc)) {
                xszjPersonFieldName = formFieldBean.getName();
            }
            if (ltcHtpslcDaMx_sybDiffTime.equals(fieldDesc)) {
                sybDiffTimeFieldName = formFieldBean.getName();
            }
            if (ltcHtpslcDaMx_jfDiffTime.equals(fieldDesc)) {
                jfDiffTimeFieldName = formFieldBean.getName();
            }
            if (ltcHtpslcDaMx_fwDiffTime.equals(fieldDesc)) {
                fwDiffTimeFieldName = formFieldBean.getName();
            }
            if (ltcHtpslcDaMx_cwDiffTime.equals(fieldDesc)) {
                cwDiffTimeFieldName = formFieldBean.getName();
            }
            if (ltcHtpslcDaMx_cpDiffTime.equals(fieldDesc)) {
                cpDiffTimeFieldName = formFieldBean.getName();
            }
            if (ltcHtpslcDaMx_xszjDiffTime.equals(fieldDesc)) {
                xszjDiffTimeFieldName = formFieldBean.getName();
            }
            if (ltcHtpsDaMx_fqTime.equals(fieldDesc)){
                fqTimeFieldName = formFieldBean.getName();
            }
            if (ltcHtpslcDaMx_Time.equals(fieldDesc)){
                TimeFieldName = formFieldBean.getName();
            }
            //            if (!"".equals(fieldDesc)) {
            //                String fieldName = formFieldBean.getName();
            //                stb.append(fieldName).append(",");
            //            }
        }
        List<FormTableBean> subTableBean = formBean.getSubTableBean();
        String tableName = "";
        for (int i = 0; i < subTableBean.size(); i++) {
            if ("平均时间明细信息".equals(subTableBean.get(i).getDisplay())) {
                tableName = subTableBean.get(i).getTableName();
            }
        }
        List<String> sqlMap=new ArrayList<>();
        String jfSql="";
        String fwSql="";
        String cwSql="";
        String cpSql="";
        if (type.equals("事业部")){
            stb.append(sybPersonFieldName+" as person, (sum("+sybDiffTimeFieldName+")/count(1)) as time from "+tableName+" where "+sybPersonFieldName+" is not null and "+fqTimeFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'"+" GROUP BY "+sybPersonFieldName);
            log.info(type+"-----sql语句：" + stb);
            sqlMap.add(stb.toString());
        }else if (type.equals("四要素")){
            jfSql=stb+jfPersonFieldName+" as person, (sum("+jfDiffTimeFieldName+")/count(1)) as time from "+tableName+" where "+jfPersonFieldName+" is not null and "+fqTimeFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'"+" GROUP BY "+jfPersonFieldName;
            log.info(type+"交付-----sql语句：" + jfSql);
            fwSql=stb+fwPersonFieldName+" as person, (sum("+fwDiffTimeFieldName+")/count(1)) as time from "+tableName+" where "+fwPersonFieldName+" is not null and "+fqTimeFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'"+" GROUP BY "+fwPersonFieldName;
            log.info(type+"法务-----sql语句：" + fwSql);
            cwSql=stb+cwPersonFieldName+" as person, (sum("+cwDiffTimeFieldName+")/count(1)) as time from "+tableName+" where "+cwPersonFieldName+" is not null and "+fqTimeFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'"+" GROUP BY "+cwPersonFieldName;
            log.info(type+"财务-----sql语句：" + cwSql);
            cpSql=stb+cpPersonFieldName+" as person, (sum("+cpDiffTimeFieldName+")/count(1)) as time from "+tableName+" where "+cpPersonFieldName+" is not null and "+fqTimeFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'"+" GROUP BY "+cpPersonFieldName;
            log.info(type+"产品-----sql语句：" + cpSql);
            sqlMap.add(jfSql);
            sqlMap.add(fwSql);
            sqlMap.add(cwSql);
            sqlMap.add(cpSql);
        }else{
            stb.append(xszjPersonFieldName+" as person, "+xszjDiffTimeFieldName +" as time ,"+TimeFieldName+" as times "+" from "+tableName+" where "+xszjPersonFieldName+" is not null and "+fqTimeFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'");
            log.info(type+"-----sql语句：" + stb);
            sqlMap.add(stb.toString());
        }

        List<Map<String,Object>> resMapList=new ArrayList<>();

        for (String sql:sqlMap){
            List allList = capDataListSqlHelpManager.selectDataList(sql, null);
            if (!allList.isEmpty()){
                if (type.equals("销售总监")){
                    resMapList.addAll(getxszjTimes());
                }else {
                    for (int i = 0; i < allList.size(); i++) {
                        Map<String, Object> dataMap = (Map<String, Object>) allList.get(i);
                        V3xOrgMember v3xOrgMember = orgManager.getMemberById(Long.parseLong((String) dataMap.get("person")));
                        String memberName = v3xOrgMember.getName();
                        Map<String, Object> resMap = new HashMap<>();
                        resMap.put("memberName", memberName);
                        resMap.put("day", dataMap.get("time"));
                        resMap.put("url", "/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-7740745912921274483&formId=-4637401560272321472&type=baseInfo&tag=1728473601298&recommendMenuId=5609606026495936269&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-984500450664494687");
                        resMap.put("hour", roundToOneDecimalPlace(Double.parseDouble(dataMap.get("time").toString()) * 24));
                        resMapList.add(resMap);
                    }
                }
            }
        }
        return resMapList;
    }




    @Override
    public Map<String, Object> getFiveTest(String type) throws BusinessException, SQLException, ParseException {
        Map<String,Object> map=new HashMap<>();
        if (type.equals("1")){
            map.put("lxps",getProjectReview());
        }else if(type.equals("2")){
            map.put("htps",getContractReview());
        }else if(type.equals("3")){
            map.put("yxshx",getPreSaleWriteOff());
        }else if(type.equals("4")){
            map.put("bzpo",getStandardPoDo());
        }else{
            map.put("xqxd",getPlaceAnOrder());
        }
        return map;
    }

    @Override
    public String getProjectsUrl() {
        String url = "";
        String newurl = "";
        String tableName = "";
        String id = "";
        String formId = "";

        try {
            /*获取项目研发档案表中对应字段*/
            String yfdd_formCode = "LTCYFXMDA_001";
            FormBean formBean = cap4FormManager.getFormByFormCode(yfdd_formCode);
            if (formBean!=null) {
                formId=StrKit.str(formBean.getId());
                CAPFormDefinition capFormDefinition = capFormDefinitionManager.selectById(formBean.getId());
                String appbindInfo = capFormDefinition.getAppbindInfo();
                Map<String,Object> jsonObject = JSONObject.parseObject(appbindInfo,Map.class);
                List<Map<String, Object>> formBindAuthList =  (List<Map<String, Object>>)jsonObject.get("formBindAuthList");
                String  tempId = StrKit.str(formBindAuthList.get(0).get("id"));
                /*测试环境链接*/
                //url = "/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&moduleId="+tempId+"&formId="+formId+"&type=baseInfo&tag=1727062281333&recommendMenuId=-3766527946272912759&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_2675322118431327768";
                url="/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-5611791159279204808&formId=-8089591489532575132&type=baseInfo&tag=1728887380911&recommendMenuId=-3766527946272912759&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_4124317860443472294";
            }


        }catch (Exception e){
            
        }
        return url;
    }

    private void dealQueryData(List list, HashMap<String, Object> data ,List<Map<String, Object>> res ,String name){
        if (list.size()>0){
            Map<String,Object> map=(Map)list.get(0);
            //String formRecordid = StrKit.str(map.get("FORM_RECORDID"));
            data.put(name+"OUTLIMITSUM",StrKit.str(map.get("out_limit_sum")));
            data.put(name+"APPROACHLIMITSUM",StrKit.str(map.get("approach_limit_sum")));
            /*处理各个里程碑的穿透链接*/
            /*项目启动*/
            if("xmqd".equals(name)){
                data.put(name+"cqUrl", FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.xmqdcqurl"))));
                data.put(name+"lqUrl",FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.xmqdlqurl"))));
            } else if ("bqyd".equals(name)) {
                /*标前引导*/
                data.put(name+"cqUrl",FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.bqydcqurl"))) );
                data.put(name+"lqUrl", FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.bqydlqurl"))));
            }else if ("jjfa".equals(name)) {
                /*制定并提交解决方案*/
                data.put(name+"cqUrl", FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.jjfacqurl"))) );
                data.put(name+"lqUrl",  FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.jjfalqurl"))));
            }else if ("hrjd".equals(name)) {
                /*合同谈判与签订*/
                data.put(name+"cqUrl",  FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.hrjdcqurl"))));
                data.put(name+"lqUrl",  FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.hrjdlqurl"))));
            }else if ("gbxm".equals(name)) {
                /*标前引导*/
                data.put(name+"cqUrl", FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.gbxmcqurl"))));
                data.put(name+"lqUrl",  FormCap4Kit.getBbAddres(StrKit.str(AppContext.getSystemProperty("ltc.gbxmlqurl"))));
            }

        }
    }

    /**
     * 查询子阶段的数据
     *
     * 该方法根据给定的阶段输出限制、阶段接近限制、子表名、计划状态、监测状态枚举值、子ID、进度枚举值，
     * 查询并返回相关数据列表它主要用于LTC预警中心的明细查询
     *
     * @param phaseOutLimit 阶段输出限制字段名
     * @param phaseApproachLimit 阶段接近限制字段名
     * @param subTableName 子表名
     * @param planStatu 计划状态
     * @param jhzt_jxz 监测状态枚举值，用于查询对应状态的记录
     * @param subId 子ID，用于定位特定的记录
     * @param ctpEnumItem 进度的枚举值对象，表示特定的进度状态
     * @param progress 进度字段名
     * @return 返回查询到的数据列表
     * @throws BusinessException 可能抛出的业务异常
     * @throws SQLException 可能抛出的SQL异常
     */
    private List querySubPhaseData(String trueData,String planData,String subTableName,String planStatu,CtpEnumItem jhzt_ywc,CtpEnumItem jhzt_qx
        ,CtpEnumItem ctpEnumItem,String progress,String sjc) throws BusinessException, SQLException {
        LocalDateTime nowStr = LocalDateTime.now();
        String now = nowStr.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        StringBuilder newQueryPhaseProgressSql=new StringBuilder();
        // 初始化SQL查询字符串
        StringBuilder queryPhaseProgressSql=new StringBuilder();
        // 拼接SQL查询语句，计算阶段输出和阶段接近的总和
        queryPhaseProgressSql.append(" select COUNT(1) as out_limit_sum ")
                .append(" FROM ").append(subTableName).append(" where ").append(planStatu).append(" != ").append(jhzt_qx.getId()).append(" and ").append(planStatu).append(" != ").append(jhzt_ywc.getId())
                .append(" and ").append(progress).append(" = ").append(ctpEnumItem.getId())
                .append( " AND ").append("DATEDIFF(MINUTE, '"+now+"',"+planData+") <=0");
        newQueryPhaseProgressSql.append(" select").append(" COUNT(1) AS approach_limit_sum")
                .append(" FROM ").append(subTableName).append(" where ").append(planStatu).append(" != ").append(jhzt_qx.getId()).append(" and ").append(planStatu).append(" != ").append(jhzt_ywc.getId())
                .append(" and ").append(progress).append(" = ").append(ctpEnumItem.getId())
                .append( " AND ").append("DATEDIFF(MINUTE, '"+now+"',"+planData+") >=0")
                .append( " AND ").append("DATEDIFF(DAY, '"+now+"',"+planData+") <= ").append(sjc);

        // 记录查询SQL日志
        log.info("ltc预警中心明细表表查询sql"+queryPhaseProgressSql.toString());
        log.info("ltc预警中心明细表表查询sql"+newQueryPhaseProgressSql.toString());
        // 执行SQL查询，并获取结果列表
        List res = capDataListSqlHelpManager.selectDataList(queryPhaseProgressSql.toString(), null);
        List newres = capDataListSqlHelpManager.selectDataList(newQueryPhaseProgressSql.toString(), null);
        List<Map<String,Object>> result=new ArrayList<Map<String,Object>>();
        if (!res.isEmpty() && !newres.isEmpty()){
            Map<String,Object> resMap=(Map<String,Object>)res.get(0);
            Map<String,Object> newresMap=(Map<String,Object>)newres.get(0);
            Map<String, Object> map = new HashMap<>();
            map.put("out_limit_sum",resMap.get("out_limit_sum"));
            map.put("approach_limit_sum",newresMap.get("approach_limit_sum"));
            result.add(map);
        }


        // 返回查询结果
        return  result;
    }




    /*根据条件查询开发项目档案中的部门或者人员*/
    /**
     * 根据参数查询部门或人员信息
     *
     * @param parm 查询参数，可以是产品经理、客户经理或部门
     * @return 包含查询结果的列表，每个结果是一个包含名称和ID的Map
     * @throws BusinessException 业务异常
     * @throws SQLException SQL异常
     */
    private List<Map<String,Object>> findDepOrPerson(String parm) throws BusinessException, SQLException {
        // 初始化结果列表
        List<Map<String, Object>> res = new ArrayList<>();
        // 获取系统属性中定义的表代码
        String fromCode = StrKit.str(AppContext.getSystemProperty("ltc.yfxmda_formCode"));
        // 根据表代码获取表单对象
        FormBean formBean = cap4FormManager.getFormByFormCode(fromCode);
        // 获取查询参数对应的系统属性
        String dep = AppContext.getSystemProperty("ltc.ltc_query_dep");
        String pro = AppContext.getSystemProperty("ltc.ltc_query_pro");
        String cus = AppContext.getSystemProperty("ltc.ltc_query_cus");
        // 初始化用于存放结果的Map
        HashMap<String, Object> map = new HashMap<String, Object>();

        // 获取需要查询的字段
        String proName = formBean.getFieldBeanByDisplay("产品经理").getName();
        String custName = formBean.getFieldBeanByDisplay("客户经理").getName();
        String depName = formBean.getFieldBeanByDisplay("所属销售部门").getName();
        String tableName = formBean.getFieldBeanByDisplay("所属销售部门").getOwnerTableName();

        // 组装查询sql
        StringBuilder queryPersonSql = new StringBuilder();
        queryPersonSql.append("select ").append(proName).append(" as pro, ").append(custName).append(" as cus from ").append(tableName);
        StringBuilder queryDepSql = new StringBuilder();
        queryDepSql.append("select ").append(depName).append(" as dep from ").append(tableName);

        // 记录查询sql日志
        log.info("产品/客户经理查询sql: " + queryPersonSql);
        log.info("所属部门查询sql: " + queryDepSql);

        // 执行查询sql
        List list = capDataListSqlHelpManager.selectDataList(queryPersonSql.toString(), null);
        List depList = capDataListSqlHelpManager.selectDataList(queryDepSql.toString(), null);

        // 记录查询结果日志
        log.info("产品/客户经理查询sql结果: " + list.size() + " 所属部门查询sql结果: " + depList.size());

        // 如果查询结果为空，则返回null
        if (list.isEmpty()) {
            return null;
        }
        if (depList.isEmpty()) {
            return null;
        }

        // 如果是查询人员
        if (parm.equals(pro) || parm.equals(cus)) {
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> zbMap = (Map<String, Object>) list.get(i);
                String id = "";
                if (parm.equals(pro)) {
                    id = StrKit.str(zbMap.get("pro"));
                } else {
                    id = StrKit.str(zbMap.get("cus"));
                }
                V3xOrgMember member = orgManager.getMemberById(Long.parseLong(id));
                if (member != null) {
                    map.put(member.getName(), id);
                    res.add(map);
                }
            }
        } else if (parm.equals(dep)) {
            // 如果是查询部门
            for (int i = 0; i < depList.size(); i++) {
                Map<String, Object> depMap = (Map<String, Object>) depList.get(i);
                String depId = StrKit.str(depMap.get("pro"));
                V3xOrgDepartment department = orgManager.getDepartmentById(Long.parseLong(depId));
                if (department != null) {
                    map.put(department.getName(), depId);
                    res.add(map);
                }
            }
        }

        // 返回查询结果
        return res;
    }


    /**
     * 五个一中立项评审
     * @return
     */
    public Map<String,Object> getProjectReview() throws BusinessException, SQLException {
        // 获取五个一立项评审数据
        Map<String, Object> resMap = getFiveofOneProjectPublic(AppContext.getSystemProperty("ltc.ltcLxpsDaTemp"), "LTC立项评审",0.04,"ltc.ltc_sjc");
        // 获取五个一立项评审平均处理时间
        resMap.put("pjclsj",getCalculateTime(AppContext.getSystemProperty("ltc.ltcLxpsDaTemp"),"立项评审","ltc.ltc_pjsj"));
        return resMap;
    }

    /**
     * 五个一中合同评审
     * @return
     */
    public Map<String,Object> getContractReview() throws BusinessException, SQLException {
        // 获取五个一中合同评审数据
        Map<String, Object> resMap = getFiveofOneProjectPublic(AppContext.getSystemProperty("ltc.ltcHtpslcDaTemp"), "LTC合同评审",0.041667,"ltc.ltc_pjsj");
        // 获取五个一立项评审平均处理时间
        resMap.put("pjclsj",getCalculateTime(AppContext.getSystemProperty("ltc.ltcHtpslcDaTemp"),"合同评审","ltc.ltc_pjsj"));
        return resMap;
    }



    /**
     * 五个一中预销售核销
     * @return
     */
    public Map<String,Object> getPreSaleWriteOff() throws BusinessException, SQLException, ParseException {
        String name="预销售类型";
        String formCode = AppContext.getSystemProperty("ltc.ltcYxshxTemp");

        // 提取当前年份
        int thisYear = LocalDate.now().getYear();
        // 设置日期为今年的第一天
        LocalDate startOfYear = LocalDate.of(thisYear, 1, 1);
        //今年最后一天
        LocalDate endOfYear = LocalDate.of(thisYear, 12, 31);

        String bhddwl_ddsl = AppContext.getSystemProperty("ltc.bhddwl_ddsl"); //订单数量
        String bhddwl_yssl = AppContext.getSystemProperty("ltc.bhddwl_yssl"); //验收数量
        String bhddwl_cjsj = AppContext.getSystemProperty("ltc.bhddwl_cjsj"); //创建时间
        String bhddwl_yssj = AppContext.getSystemProperty("ltc.bhddwl_yssj"); //验收时间
        String bhddwl_htlx= AppContext.getSystemProperty("ltc.bhddwl_htlx");  //合同类型
        String ddzt = "DDZT";
        String bhddwl_ddbh = "DOCNUM"; //订单编号

        //枚举编码
        CtpEnumItem Enum_htlx_yxs = FormCap4Kit.getEnumByCode("YXS");  //预销售
        CtpEnumItem Enum_ddzt_zf = FormCap4Kit.getEnumByCode("zf");  //预作废
        CtpEnumItem Enum_ddzt_zz = FormCap4Kit.getEnumByCode("zz");  //终止

        //总分钟
        int fzTime= 0;
        //有时间数量
        int haveTimeCount=0;
        //根据档案编码获取formBean对象
        FormBean FormBean = cap4FormManager.getFormByFormCode(formCode);
        List<FormFieldBean> formFieldBeans = FormBean.getMasterTableBean().getFields();
        StringBuilder stb = new StringBuilder();
        stb.append("select ");
        String bhddwl_ddslFieldName="";
        String bhddwl_ysslFieldName="";
        String bhddwl_cjsjFieldName="";
        String bhddwl_yssjFieldName="";
        String bhddwl_htlxFieldName="";
        String bhddwl_ddbhFieldName="";
        String ddztFieldName="";
        for (FormFieldBean formFieldBean : formFieldBeans) {
            String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
            if (bhddwl_ddsl.equals(fieldDesc)) {
                bhddwl_ddslFieldName = formFieldBean.getName();
            }
            if (bhddwl_yssl.equals(fieldDesc)) {
                bhddwl_ysslFieldName = formFieldBean.getName();
            }
            if (bhddwl_cjsj.equals(fieldDesc)) {
                bhddwl_cjsjFieldName = formFieldBean.getName();
            }
            if (bhddwl_yssj.equals(fieldDesc)) {
                bhddwl_yssjFieldName = formFieldBean.getName();
            }
            if (bhddwl_htlx.equals(fieldDesc)){
                bhddwl_htlxFieldName = formFieldBean.getName();
            }
            if (bhddwl_ddbh.equals(fieldDesc)){
                bhddwl_ddbhFieldName = formFieldBean.getName();
            }
            if (ddzt.equals(fieldDesc)){
                ddztFieldName = formFieldBean.getName();
            }
            if (!"".equals(fieldDesc)) {
                String fieldName = formFieldBean.getName();
                stb.append(fieldName).append(",");
            }
        }
        /* 删除最后一个逗号，完成SQL查询语句的构建 */
        String str = stb.substring(0, stb.length() - 1);
        String tableName = FormBean.getMasterTableBean().getTableName();
        String allCountByIdsSql= str + " from " + tableName
                +" where "+bhddwl_htlxFieldName+" = "+Enum_htlx_yxs.getId()
                +" and "+bhddwl_cjsjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'"
                +" and "+bhddwl_ddbhFieldName+" not like '%RS%' and "
                +ddztFieldName+" not in ('"+Enum_ddzt_zf.getId()+"','"+Enum_ddzt_zz.getId()+"')"
                +" and "+bhddwl_cjsjFieldName+" > '2024-9-30 00:00:00'";
        log.info(name+"总发起数-----sql语句：" + allCountByIdsSql);
        //总发起数
        List<Map<String, Object>> allList = capDataListSqlHelpManager.selectDataList(allCountByIdsSql, null);
        allList = deduplicateByFieldName(allList, bhddwl_ddbhFieldName);
        //平均时间
        if (!allList.isEmpty()) {
            for (int i = 0; i < allList.size(); i++) {
                Map<String, Object> dataMap = (Map<String, Object>) allList.get(i);
                String createDateStr = StrKit.diffTime(dataMap.get(bhddwl_cjsjFieldName));
                String zxwcDateStr = StrKit.diffTime(dataMap.get(bhddwl_yssjFieldName));
                if(!createDateStr.equals("") && !zxwcDateStr.equals("")){
                    haveTimeCount++;
                    //计算两个时间相差分
                    fzTime += DateUtil.getTime(createDateStr, zxwcDateStr);
                }
            }
        }
        String allWrireOffCountByIdsSql= str + " from " + tableName
                + " where "+bhddwl_ddslFieldName+" <= "+bhddwl_ysslFieldName
                +" and "+bhddwl_htlxFieldName+" = "+Enum_htlx_yxs.getId()+" and "
                +bhddwl_cjsjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'"
                +" and "+bhddwl_ddbhFieldName+" not like '%RS%' and "
                +ddztFieldName+" not in ('"+Enum_ddzt_zf.getId()+"','"+Enum_ddzt_zz.getId()+"')"
                +" and "+bhddwl_cjsjFieldName+" > '2024-9-30 00:00:00'";
        log.info(name+"总核销数-----sql语句：" + allWrireOffCountByIdsSql);
        //总核销数
        List<Map<String, Object>>  allFinishList = capDataListSqlHelpManager.selectDataList(allWrireOffCountByIdsSql, null);
        allFinishList= deduplicateByFieldName(allFinishList, bhddwl_ddbhFieldName);
        String allNoWrireOffCountByIdsSql= str + " from " + tableName
                + " where "+bhddwl_ddslFieldName+" - "+bhddwl_ysslFieldName+" >0 and "
                +bhddwl_htlxFieldName+" = "+Enum_htlx_yxs.getId()+" and "
                +bhddwl_cjsjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'"
                +" and "+bhddwl_ddbhFieldName+" not like '%RS%' and "
                +ddztFieldName+" not in ('"+Enum_ddzt_zf.getId()+"','"+Enum_ddzt_zz.getId()+"')"
                +" and "+bhddwl_cjsjFieldName+" > '2024-9-30 00:00:00'";
        log.info(name+"总未核销-----sql语句：" + allNoWrireOffCountByIdsSql);
        //总未核销
        List<Map<String, Object>>  allNoFinishList = capDataListSqlHelpManager.selectDataList(allNoWrireOffCountByIdsSql, null);
        allNoFinishList= deduplicateByFieldName(allNoFinishList, bhddwl_ddbhFieldName);

        String allExceedYhxCountByIdsSql= null;
        String allNoExceedYhxCountByIdsSql= null;
        if (DATABASETYPE.equals("mysql")){
            //单位：分钟 一个月按照三十天计算 30天=43200分钟
            allNoExceedYhxCountByIdsSql= str + " from " + tableName
                + " where  "
                +bhddwl_ddslFieldName+" > "+bhddwl_ysslFieldName
               +" and TIMESTAMPDIFF(MINUTE, "+bhddwl_cjsjFieldName+" , CURRENT_TIMESTAMP) > 43200 and "+bhddwl_yssjFieldName+" is null";

            allExceedYhxCountByIdsSql= str + " from " + tableName + " where  "
                +bhddwl_ddslFieldName+" <= "+bhddwl_ysslFieldName
                +" and TIMESTAMPDIFF(MINUTE, "+bhddwl_cjsjFieldName+" , "+bhddwl_yssjFieldName+") > 43200";

        }else{
            //单位：分钟 一个月按照三十天计算 30天=43200分钟
            allNoExceedYhxCountByIdsSql= str + " from " + tableName
                + " where "
                +bhddwl_ddslFieldName+" > "+bhddwl_ysslFieldName
                +" and DATEDIFF(MINUTE, "+bhddwl_cjsjFieldName+" , CURRENT_TIMESTAMP) > 43200 and "
                    +bhddwl_yssjFieldName+" is null and "+bhddwl_htlxFieldName+" = "+Enum_htlx_yxs.getId()+" and "
                    +bhddwl_cjsjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'"
                    +" and "+bhddwl_ddbhFieldName+" not like '%RS%'  and "
                    +ddztFieldName+" not in ('"+Enum_ddzt_zf.getId()+"','"+Enum_ddzt_zz.getId()+"')"
                    +" and "+bhddwl_cjsjFieldName+" > '2024-9-30 00:00:00'";

            allExceedYhxCountByIdsSql= str + " from " + tableName + " where  "
                +bhddwl_ddslFieldName+" <= "+bhddwl_ysslFieldName
                +" and DATEDIFF(MINUTE, "+bhddwl_cjsjFieldName+" , "+bhddwl_yssjFieldName+") > 43200 and "
                    +bhddwl_htlxFieldName+" = "+Enum_htlx_yxs.getId()+" and "
                    +bhddwl_cjsjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'"
                    +" and "+bhddwl_ddbhFieldName+" not like '%RS%' and "
                    +ddztFieldName+" not in ('"+Enum_ddzt_zf.getId()+"','"+Enum_ddzt_zz.getId()+"')"
                    +" and "+bhddwl_cjsjFieldName+" > '2024-9-30 00:00:00'";
        }

        log.info(name+"超期已核销数-----sql语句：" + allExceedYhxCountByIdsSql);
        //超期已核销数
        List<Map<String, Object>>  allExceedYhxList = capDataListSqlHelpManager.selectDataList(allExceedYhxCountByIdsSql, null);
        allExceedYhxList= deduplicateByFieldName(allExceedYhxList, bhddwl_ddbhFieldName);

        log.info(name+"超期未核销数-----sql语句：" + allNoExceedYhxCountByIdsSql);
        //超期未核销数
        List<Map<String, Object>>  allNoExceedYhxList = capDataListSqlHelpManager.selectDataList(allNoExceedYhxCountByIdsSql, null);
        allNoExceedYhxList= deduplicateByFieldName(allNoExceedYhxList, bhddwl_ddbhFieldName);

        Map<String,Object> map=new HashMap<>();
        map.put("allList",allList.size());
        map.put("allListUrl", FormCap4Kit.getBbAddres("LTC预销售核销-总发起数"));
        if (haveTimeCount==0){
            map.put("pjclsj",0);
        }else{
            map.put("pjclsj",roundToOneDecimalPlace((double)(fzTime / haveTimeCount) / 1440));
        }
        map.put("haveTimeCount",haveTimeCount);
        map.put("allFinishList",allFinishList.size());
        map.put("allNoFinishList",allNoFinishList.size());
        map.put("allExceedYpsList",allExceedYhxList.size());
        map.put("allNoExceedYpsList",allNoExceedYhxList.size());
        if (AppContext.getSystemProperty("brwh.eqbdyym").equals("https://smlopenapi.esign.cn")){
            //测试环境
            map.put("allNoExceedYpsListUrl", FormCap4Kit.getBbAddres("LTC预销售核销-超期未核销"));
            map.put("allExceedYpsListUrl", FormCap4Kit.getBbAddres("LTC预销售核销-超期核销数"));
            map.put("allNoFinishListUrl", FormCap4Kit.getBbAddres("LTC预销售核销-总未核销"));
            map.put("allFinishListUrl", FormCap4Kit.getBbAddres("LTC预销售核销-总核销数"));
        }else{
            //正式环境
            map.put("allNoExceedYpsListUrl", FormCap4Kit.getBbAddres("LTC预销售核销-超期未核销"));
            map.put("allExceedYpsListUrl", FormCap4Kit.getBbAddres("LTC预销售核销-超期核销数"));
            map.put("allNoFinishListUrl", FormCap4Kit.getBbAddres("LTC预销售核销-总未核销"));
            map.put("allFinishListUrl", FormCap4Kit.getBbAddres("LTC预销售核销-总核销数"));

        }
        /*处理平均冲销时间  */
        //冲销订单档案FormBean
        FormBean cxddFormBean = cap4FormManager.getFormByFormCode("XSHTDA001");
        //预销售销售总监审批时间
        String cxdd_spsj = "";
        //预销售审批结束时间
        String cxdd_jssj = "";
        //主表表名
        String cxdd_tbble_name = "";
        //冲销状态
        String xshtda_cxzt = "";
        //平均冲销时间
        String cxdd_pjcxsj = "0";
        //时间差
        String cxdd_sjc = "";
        if (cxddFormBean != null) {
            cxdd_spsj = cxddFormBean.getFieldBeanByDisplay("预销售销售总监审批时间").getName();
            cxdd_jssj = cxddFormBean.getFieldBeanByDisplay("预销售审批结束时间").getName();
            cxdd_sjc = cxddFormBean.getFieldBeanByDisplay("时间差").getName();
            xshtda_cxzt= cxddFormBean.getFieldBeanByDisplay("冲销状态").getName();
            cxdd_tbble_name = cxddFormBean.getFieldBeanByDisplay("时间差").getOwnerTableName();
            CtpEnumItem Enum_xshtda_cxzt_ycx = FormCap4Kit.getEnumByCode("YCX");//已冲销

            /*时间差/个数 (预销售审批结束时间 和 预销售销售总监审批时间 不为空)*/
            //    组装查询sql
            String cxddSql = "SELECT SUM(" + cxdd_sjc + ") AS sumsjc, COUNT(" + cxdd_sjc + ") AS countsjc FROM "+cxdd_tbble_name+" WHERE ("
                    + cxdd_spsj + " IS NOT NULL and "
                    + cxdd_jssj + " IS NOT NULL ) and "+xshtda_cxzt +"= '"+Enum_xshtda_cxzt_ycx.getId()+"'";
            log.info("平均冲销时间查询sql" + cxddSql);
            List cxddRes = capDataListSqlHelpManager.selectDataList(cxddSql, null);
            //平均冲销时间
            if (!cxddRes.isEmpty()) {
                for (int i = 0; i < cxddRes.size(); i++) {
                    Map<String, Object> dataMap = (Map<String, Object>) cxddRes.get(0);
                    //符合条件的时间差总和/个数
                    Double countsjc = Double.parseDouble(StrKit.diffTime(dataMap.get("countsjc")).equals("")?"0":StrKit.diffTime(dataMap.get("countsjc")));
                    Double sumsjc = Double.parseDouble(StrKit.diffTime(dataMap.get("sumsjc")).equals("")?"0":StrKit.diffTime(dataMap.get("sumsjc")));
                    if (countsjc != 0d) {
                        cxdd_pjcxsj = StrKit.str(roundToOneDecimalPlace(sumsjc / countsjc));
                    }
                }
            }
        }
        map.put("averageCxsjUrl", FormCap4Kit.getBbAddres("LTC-平均冲销时间"));
        map.put("averageCxsj", cxdd_pjcxsj);
        return map;
    }

    /**
     * 五个一中标准po处理
     * @return
     */
    public Map<String,Object> getStandardPoDo() throws BusinessException, SQLException, ParseException {
        String name="标准po处理";

        // 提取当前年份
        int thisYear = LocalDate.now().getYear();
        // 设置日期为今年的第一天
        LocalDate startOfYear = LocalDate.of(thisYear, 1, 1);
        //今年最后一天
        LocalDate endOfYear = LocalDate.of(thisYear, 12, 31);

        String formCode = AppContext.getSystemProperty("ltc.ltcBzpoclTemp");

        String bzpocl_ddlx = AppContext.getSystemProperty("ltc.xsdd_htlx"); //合同类型
        String kssj = AppContext.getSystemProperty("ltc.ltc_kssj"); //开始时间
        String jssj = AppContext.getSystemProperty("ltc.ltc_jssj"); //结束时间
        //枚举编码
        String bzpocl_bzpo = AppContext.getSystemProperty("ltc.htlx_bzkj"); //标准po
        CtpEnumItem Enum_bzpocl_bzpo = FormCap4Kit.getEnumByCode(bzpocl_bzpo);

        List<Long> revokeAndDraftIds = findRevokeAndDraftIds(formCode);
        StringBuilder sb = new StringBuilder();
        for (Long id : revokeAndDraftIds) {
            if (sb.length() > 0) {
                sb.append(",");
            }
            sb.append(id);
        }
        String result = sb.toString();

        //总分钟
        long fzTime= 0;
        //有时间数量
        int haveTimeCount=0;
        //根据档案编码获取formBean对象
        FormBean FormBean = cap4FormManager.getFormByFormCode(formCode);
        List<FormFieldBean> formFieldBeans = FormBean.getMasterTableBean().getFields();
        StringBuilder stb = new StringBuilder();
        stb.append("select id, ");
        String bzpocl_ddlxFieldName="";
        String kssjFieldName="";
        String jssjFieldName="";
        for (FormFieldBean formFieldBean : formFieldBeans) {
            String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
            if (bzpocl_ddlx.equals(fieldDesc)) {
                bzpocl_ddlxFieldName = formFieldBean.getName();
            }
            if (kssj.equals(fieldDesc)) {
                kssjFieldName = formFieldBean.getName();
            }
            if (jssj.equals(fieldDesc)) {
                jssjFieldName = formFieldBean.getName();
            }
            if (!"".equals(fieldDesc)) {
                String fieldName = formFieldBean.getName();
                stb.append(fieldName).append(",");
            }
        }
        /* 删除最后一个逗号，完成SQL查询语句的构建 */
        String str = stb.substring(0, stb.length() - 1);
        String tableName = FormBean.getMasterTableBean().getTableName();
        String allCountByIdsSql= str +" , DATEDIFF(MINUTE,"+kssjFieldName +","+jssjFieldName+") as difftime "+ " from " + tableName
                + " where " +bzpocl_ddlxFieldName+" = '"+Enum_bzpocl_bzpo.getId()+"' and "+kssjFieldName+" is not null"
                +" and " +kssjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'"
                +" and id  in("+result+")"
                +" and "+kssjFieldName+" > '2024-9-30 00:00:00'";
        log.info(name+"总发起数-----sql语句：" + allCountByIdsSql);
        //总发起数
        List allList = capDataListSqlHelpManager.selectDataList(allCountByIdsSql, null);
        //平均时间
        if (!allList.isEmpty()) {
            for (int i = 0; i < allList.size(); i++) {
                Map<String, Object> dataMap = (Map<String, Object>) allList.get(i);
                String createDateStr = StrKit.diffTime(dataMap.get(kssjFieldName));
                String zxwcDateStr = StrKit.diffTime(dataMap.get(jssjFieldName));
                if(!createDateStr.equals("") && !zxwcDateStr.equals("")){
                    haveTimeCount++;
                    //计算两个时间相差分
                    fzTime += Long.parseLong(dataMap.get("difftime").toString());
                }
            }
        }
        log.info(name+"平均时间:"+fzTime);
        log.info(name+"haveTimeCount统计数量:"+haveTimeCount);

        String allFinishCountByIdsSql= str + " from " + tableName
                + " where  " +bzpocl_ddlxFieldName+" = '"+Enum_bzpocl_bzpo.getId()+"' and "
                +kssjFieldName+" is not null and "+jssjFieldName+" is not null "
                +" and "
                +kssjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'"
                +" and id  in("+result+")"
                +" and "+kssjFieldName+" > '2024-9-30 00:00:00'";
        log.info(name+"总完成数-----sql语句：" + allFinishCountByIdsSql);
        //总完成数
        List allFinishList = capDataListSqlHelpManager.selectDataList(allFinishCountByIdsSql, null);

        String allNoFinishCountByIdsSql= str + " from " + tableName
                + " where " +bzpocl_ddlxFieldName+" = '"+Enum_bzpocl_bzpo.getId()+"' and "
                +kssjFieldName+" is not null and "+jssjFieldName+" is null "
                +" and "
                +kssjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'"
                +" and id  in("+result+")"
                +" and "+kssjFieldName+" > '2024-9-30 00:00:00'";
        log.info(name+"总未完成数-----sql语句：" + allNoFinishCountByIdsSql);
        //总未完成数
        List allNoFinishList = capDataListSqlHelpManager.selectDataList(allNoFinishCountByIdsSql, null);

        String allExceedYpsCountByIdsSql=null;
        String allNoExceedYpsCountByIdsSql=null;
        if (DATABASETYPE.equals("mysql")){

            allExceedYpsCountByIdsSql= str + " from " + tableName + " where "
                +bzpocl_ddlxFieldName+" = '"+Enum_bzpocl_bzpo.getId()
                +"' and  TIMESTAMPDIFF(MINUTE,"+kssjFieldName+", "+jssjFieldName+") > 1";


            allNoExceedYpsCountByIdsSql= str + " from " + tableName + " where "
                +bzpocl_ddlxFieldName+" = '"+Enum_bzpocl_bzpo.getId()
                +"' and TIMESTAMPDIFF(MINUTE,"+kssjFieldName+", CURRENT_TIMESTAMP) > 1 and "+jssjFieldName+" is null";
        }else{

            allExceedYpsCountByIdsSql= str + " from " + tableName
                    + " where " +bzpocl_ddlxFieldName+" = '"+Enum_bzpocl_bzpo.getId()
                    +"' and  DATEDIFF(MINUTE,"+kssjFieldName+", "+jssjFieldName+") > 1"
                    +" and "
                    +kssjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'"
                    +" and id  in("+result+")"
                    +" and "+kssjFieldName+" > '2024-9-30 00:00:00'";

            allNoExceedYpsCountByIdsSql= str + " from " + tableName
                    + " where " +bzpocl_ddlxFieldName+" = '"+Enum_bzpocl_bzpo.getId()
                    +"' and DATEDIFF(MINUTE,"+kssjFieldName+", CURRENT_TIMESTAMP) > 1 and "+jssjFieldName+" is null"
                    +" and "
                    +kssjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'"
                    +" and id  in("+result+")"
                    +" and "+kssjFieldName+" > '2024-9-30 00:00:00'";
        }

        log.info(name+"超期已评审-----sql语句：" + allExceedYpsCountByIdsSql);
        //超期已评审数
        List allExceedYpsList = capDataListSqlHelpManager.selectDataList(allExceedYpsCountByIdsSql, null);


        log.info(name+"超期未评审-----sql语句：" + allNoExceedYpsCountByIdsSql);
        //超期未评审数
        List allNoExceedYpsList = capDataListSqlHelpManager.selectDataList(allNoExceedYpsCountByIdsSql, null);

        Map<String,Object> map=new HashMap<>();
        map.put("allList",allList.size());
        map.put("allListUrl",FormCap4Kit.getBbAddres("LTC标准PO处理-总发起数"));

        map.put("allFinishList",allFinishList.size());
        map.put("allFinishListUrl",FormCap4Kit.getBbAddres("LTC标准PO处理-总完成数"));

        map.put("allNoFinishList",allNoFinishList.size());
        map.put("allNoFinishListUrl",FormCap4Kit.getBbAddres("LTC标准PO处理-总未完成"));

        map.put("allExceedYpsList",allExceedYpsList.size());
        map.put("allExceedYpsListUrl",FormCap4Kit.getBbAddres("LTC标准PO处理-超期评审数"));

        map.put("allNoExceedYpsList",allNoExceedYpsList.size());
//        map.put("allNoExceedYpsListUrl",FormCap4Kit.getBbAddres("LTC标准PO处理-总发起数"));
        if (haveTimeCount==0){
            map.put("pjclsj", 0);
        }else{
            map.put("pjclsj", roundToOneDecimalPlace((double)fzTime / haveTimeCount));
        }
        // 标准PO 超期未评审
        map.put("allNoExceedYpsListUrl",FormCap4Kit.getBbAddres("LTC标准PO处理-超期未完成"));

        return map;
    }

    /**
     * 五个一中销区下单
     * @return
     */
    public Map<String,Object> getPlaceAnOrder() throws BusinessException, SQLException {
        Map<String,Object> resMap=new HashMap<>();

        String formCode = "XSBJ_001";

        // 提取当前年份
        int thisYear = LocalDate.now().getYear();
        // 设置日期为今年的第一天
        LocalDate startOfYear = LocalDate.of(thisYear, 1, 1);
        //今年最后一天
        LocalDate endOfYear = LocalDate.of(thisYear, 12, 31);

        String kssj = AppContext.getSystemProperty("ltc.ltc_kssj"); //开始时间
        String jssj = AppContext.getSystemProperty("ltc.ltc_jssj"); //结束时间
        String sjc = AppContext.getSystemProperty("ltc.ltc_sjc"); //时间差
        //枚举编码
//        String bzpocl_bzpo = AppContext.getSystemProperty("ltc.htlx_bzkj");
//        CtpEnumItem Enum_bzpocl_bzpo = FormCap4Kit.getEnumByCode(bzpocl_bzpo);
        List<Long> revokeAndDraftIds = findRevokeAndDraftIds(formCode);
        StringBuilder sb = new StringBuilder();
        for (Long id : revokeAndDraftIds) {
            if (sb.length() > 0) {
                sb.append(",");
            }
            sb.append(id);
        }
        String result = sb.toString();
        //总分钟
        int fzTime= 0;
        //含分钟总条数
        int haveTimeCount=0;
        //平均分钟数
        double averageDay = 0;
        //根据档案编码获取formBean对象
        FormBean FormBean = cap4FormManager.getFormByFormCode(formCode);
        List<FormFieldBean> formFieldBeans = FormBean.getMasterTableBean().getFields();
        StringBuilder stb = new StringBuilder();
        stb.append("select id, ");
        String kssjFieldName="";
        String jssjFieldName="";
        String sjcFieldName="";
        for (FormFieldBean formFieldBean : formFieldBeans) {
            String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());

            if (kssj.equals(fieldDesc)) {
                kssjFieldName = formFieldBean.getName();
            }
            if (jssj.equals(fieldDesc)) {
                jssjFieldName = formFieldBean.getName();
            }
            if (sjc.equals(fieldDesc)){
                sjcFieldName = formFieldBean.getName();
            }
            if (!"".equals(fieldDesc)) {
                String fieldName = formFieldBean.getName();
                stb.append(fieldName).append(",");
            }
        }
        /* 删除最后一个逗号，完成SQL查询语句的构建 */
        String str = stb.substring(0, stb.length() - 1);
        String tableName = FormBean.getMasterTableBean().getTableName();
        //统计总时长
        String allTimeSql=" select sum(DATEDIFF(MINUTE,"+kssjFieldName+", "+jssjFieldName+")) alltime, count(1) num from "
                +tableName +" where "+kssjFieldName+" is not null"
                +" and "+jssjFieldName+" is not null"
                +" and id  in("+result+")"
                +" and "+kssjFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'";
        log.info("销区下单获取统计总时长--------sql:"+allTimeSql);
        List<Map<String,Object>> allTimeList = capDataListSqlHelpManager.selectDataList(allTimeSql, null);
        if (!allTimeList.isEmpty()) {
            Map<String,Object> timeMap = allTimeList.get(0);
            //总分钟
            //总完成数
            haveTimeCount=Integer.parseInt(StrKit.str(timeMap.get("num")));
            if (haveTimeCount!=0){
                fzTime=Integer.parseInt(StrKit.str(timeMap.get("alltime")));
                averageDay = (double) (fzTime/haveTimeCount) / (60 * 24);
            }
        }
        //总发起数
        String allCountsSql=str+" from " + tableName
                +" where "+kssjFieldName+" is not null"
                +" and id in("+result+")"
                +" and "+kssjFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'";
        log.info("销区下单获取总发起数--------sql:"+allCountsSql);
        List allCountsList = capDataListSqlHelpManager.selectDataList(allCountsSql, null);

        //总未完成
        String allNoFinishCountSql=str+" from " + tableName
                +" where "+kssjFieldName+" is not null"
                +" and "+jssjFieldName+" is null"
                +" and id in("+result+")"
                +" and "+kssjFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'";
        log.info("销区下单获取总未完成--------sql:"+allNoFinishCountSql);
        List allNoFinishCountSqlList = capDataListSqlHelpManager.selectDataList(allNoFinishCountSql, null);

        //超期已完成
        String allExceedFinishCountSql=str+" from " + tableName
                +" where "+kssjFieldName+" is not null"
                +" and "+jssjFieldName+" is not null"
                +" and "+sjcFieldName+" > 1"
                +" and id in("+result+")"
                +" and "+kssjFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'";
        log.info("销区下单获取超期已完成-------sql:"+allExceedFinishCountSql);
        List allExceedFinishCountSqlList = capDataListSqlHelpManager.selectDataList(allExceedFinishCountSql, null);
        //超期未完成
        String allNoExceedFinishCountSql=str+" from " + tableName
                +" where "+kssjFieldName+" is not null"
                +" and "+jssjFieldName+" is null"
                +" and  DATEDIFF(MINUTE,"+kssjFieldName+",CURRENT_TIMESTAMP)>1440"
                +" and id in("+result+")"
                +" and "+kssjFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'";
        log.info("销区下单获取超期未完成--------sql:"+allNoExceedFinishCountSql);

        List allNoExceedFinishCountSqlList = capDataListSqlHelpManager.selectDataList(allNoExceedFinishCountSql, null);

        //总发起数
        resMap.put("allList",allCountsList.size());
        resMap.put("allListUrl",FormCap4Kit.getBbAddres("LTC销售报价-总发起数"));
        //总完成数量
        resMap.put("allFinishList",haveTimeCount);
        resMap.put("allFinishListUrl",FormCap4Kit.getBbAddres("LTC销售报价-总完成数"));
        //总未完成数量
        resMap.put("allNoFinishList",allNoFinishCountSqlList.size());
        resMap.put("allNoFinishListUrl",FormCap4Kit.getBbAddres("LTC销售报价-总未完成数"));
        //超期已完成数量
        resMap.put("allExceedYpsList",allExceedFinishCountSqlList.size());
        resMap.put("allExceedYpsListUrl",FormCap4Kit.getBbAddres("LTC销售报价-超期完成数"));
        //总超期未完成数量
        resMap.put("allNoExceedYpsList",allNoExceedFinishCountSqlList.size());
        resMap.put("allNoExceedYpsListUrl",FormCap4Kit.getBbAddres("LTC销售报价-超期未完成数"));
        // 使用 DecimalFormat 保留两位小数
        DecimalFormat df = new DecimalFormat("#.##");
        String formattedResult = df.format(averageDay);
        //平均时长
        resMap.put("pjclsj", formattedResult);
        //用来验证数据参数
        resMap.put("fzTime",fzTime);

        return resMap;
    }

    /**
     * 获取撤回和草稿的流程实例id
     * @param fromCode
     * @return
     */
    public List<Long> findRevokeAndDraftIds(String fromCode) throws BusinessException, SQLException {
        List<Long> ids = new ArrayList<>();
        //根据档案编码获取formBean对象
        CtpTemplate templeteByTemplateNumber = templateManager.getTempleteByTemplateNumber(fromCode);
        Long id = templeteByTemplateNumber.getId();
        //根据模板id查询流程实例id
        String sql="select * from col_summary where templete_id ="+ id +" and state not in(1, 2) and state is not null";
        log.info("获取撤回和草稿的流程实例id--------sql:"+sql);
        List dataList = capDataListSqlHelpManager.selectDataList(sql, null);
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> dataMap = (Map<String, Object>) dataList.get(i);
                Long fromRecordId = Long.parseLong(dataMap.get("form_recordid").toString());
                ids.add(fromRecordId);
            }
        }
        if (ids.size()==0){
            ids.add(0L);
        }
        return ids;
    }

    /**
     * 抽调出五个一中公共代码部分
     * 统计不同维度的数量
     * 注销的sql中排除了草稿和撤销流程的数据,由于报表无法实现暂时注销
     * @param formCode 模板编码
     * @param name 名称备注
     * @return 返回不同逻辑的返回值
     * @throws Exception
     */
    public Map<String, Object> getFiveofOneProjectPublic(String formCode,String name,double time,String timeType) throws BusinessException, SQLException {
        //字段备注
        String jczt = AppContext.getSystemProperty("ltc.ltc_jczt"); //决策状态
        String kssj = AppContext.getSystemProperty("ltc.ltc_kssj"); //开始时间
        String jssj = AppContext.getSystemProperty("ltc.ltc_jssj"); //结束时间
        String sjc= AppContext.getSystemProperty(timeType);//时间差
        //枚举编码
        String jczt_wjc = AppContext.getSystemProperty("ltc.jczt_wjc"); //未决策
        CtpEnumItem Enum_jczt_wjc = FormCap4Kit.getEnumByCode(jczt_wjc);
        String jczt_yjc = AppContext.getSystemProperty("ltc.jczt_yjc"); //已决策
        CtpEnumItem Enum_jczt_yjc = FormCap4Kit.getEnumByCode(jczt_yjc);

        // 提取当前年份
        int thisYear = LocalDate.now().getYear();
        // 设置日期为今年的第一天
        LocalDate startOfYear = LocalDate.of(thisYear, 1, 1);
        //今年最后一天
        LocalDate endOfYear = LocalDate.of(thisYear, 12, 31);

        //根据模板编码获取非草稿和撤销的流程实例id
//        List<Long> revokeAndDraftIds = findRevokeAndDraftIds(formCode);
        //根据档案编码获取formBean对象
        FormBean FormBean = cap4FormManager.getFormByFormCode(formCode);
        List<FormFieldBean> formFieldBeans = FormBean.getMasterTableBean().getFields();
        StringBuilder stb = new StringBuilder();
        stb.append("select id, ");
        String jcztFieldName="";
        String kssjFieldName="";
        String jssjFieldName="";
        String sjcFieldName="";
        for (FormFieldBean formFieldBean : formFieldBeans) {
            String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
            if (jczt.equals(fieldDesc)) {
                jcztFieldName = formFieldBean.getName();
            }
            if (kssj.equals(fieldDesc)) {
                kssjFieldName = formFieldBean.getName();
            }
            if (jssj.equals(fieldDesc)) {
                jssjFieldName = formFieldBean.getName();
            }
            if (sjc.equals(fieldDesc)){
                sjcFieldName = formFieldBean.getName();
            }
            if (!"".equals(fieldDesc)) {
                String fieldName = formFieldBean.getName();
                stb.append(fieldName).append(",");
            }
        }
        /* 删除最后一个逗号，完成SQL查询语句的构建 */
        String str = stb.substring(0, stb.length() - 1);
        String tableName = FormBean.getMasterTableBean().getTableName();
        String allCountByIdsSql= str + " from " + tableName + " where "+kssjFieldName+" is not null and "+kssjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'";
        log.info(name+"总发起数-----sql语句：" + allCountByIdsSql);
        //总发起数
        List allList = capDataListSqlHelpManager.selectDataList(allCountByIdsSql, null);

        String allFinishCountByIdsSql= str + " from " + tableName + " where "+jcztFieldName+" = '"+Enum_jczt_yjc.getId()+"' and "+kssjFieldName+" is not null and "+jssjFieldName+" is not null and "+kssjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'";
        log.info(name+"总完成数-----sql语句：" + allFinishCountByIdsSql);
        //总完成数
        List allFinishList = capDataListSqlHelpManager.selectDataList(allFinishCountByIdsSql, null);

        String  allNoFinishCountByIdsSql= str + " from " + tableName + " where ( "+jcztFieldName+" = '"+Enum_jczt_wjc.getId()+"' or "+jcztFieldName+" is null ) and "+kssjFieldName+" is not null  and "+kssjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'";
        log.info(name+"总未完成数-----sql语句：" + allNoFinishCountByIdsSql);
        //总未完成数
        List allNoFinishList = capDataListSqlHelpManager.selectDataList(allNoFinishCountByIdsSql, null);

        String allExceedYpsCountByIdsSql= null;
        String allNoExceedYpsCountByIdsSql= null;
        //根据数据哭类型拼接不同sql
        if (DATABASETYPE.equals("mysql")){
            //超期已评审sql
            allExceedYpsCountByIdsSql= str + " from " + tableName
                + " where  "+jcztFieldName+" = '"+Enum_jczt_yjc.getId()
                +"' and  TIMESTAMPDIFF(MINUTE,"+kssjFieldName+", "+jssjFieldName+") > 60";
            //超期未评审sql
            allNoExceedYpsCountByIdsSql= str + " from " + tableName + " where "
                +jcztFieldName+" = '"+Enum_jczt_wjc.getId()+"' and TIMESTAMPDIFF(MINUTE,"+kssjFieldName+", CURRENT_TIMESTAMP) > 60 and "+jssjFieldName+" is null";
        }else{
            //超期已评审sql
            allExceedYpsCountByIdsSql= str + " from " + tableName
                + " where  "+jcztFieldName+" = '"+Enum_jczt_yjc.getId()
                +"' and "+sjcFieldName+" > "+time+"  and "+kssjFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'";
            //超期未评审sql
            allNoExceedYpsCountByIdsSql= str + " from " + tableName + " where "
                +jcztFieldName+" = '"+Enum_jczt_wjc.getId()+"' and "+sjcFieldName+" > "+time+" and  "+kssjFieldName+" between '"+startOfYear+"' and '"+endOfYear+"'";
        }

        log.info(name+"超期已评审-----sql语句：" + allExceedYpsCountByIdsSql);
        //超期已评审数
        List allExceedYpsList = capDataListSqlHelpManager.selectDataList(allExceedYpsCountByIdsSql, null);

        log.info(name+"超期未评审-----sql语句：" + allNoExceedYpsCountByIdsSql);
        //超期未评审数
        List allNoExceedYpsList = capDataListSqlHelpManager.selectDataList(allNoExceedYpsCountByIdsSql, null);

        Map<String,Object> map=new HashMap<>();
        map.put("allList",allList.size());
        map.put("allFinishList",allFinishList.size());
        map.put("allNoFinishList",allNoFinishList.size());
        map.put("allExceedYpsList",allExceedYpsList.size());
        if (name.equals("LTC立项评审")){
            if (AppContext.getSystemProperty("brwh.sapCompanyDB").equals("CQBRWH_ZSK")){
                //正式环境
                map.put("allListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-1716404642969402171&formId=-6360989751442059508&type=baseInfo&tag=1728568481093&recommendMenuId=-6230557886414960298&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_2848759559096255948");
                map.put("allFinishListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-4275078326190584934&formId=-6360989751442059508&type=baseInfo&tag=1728568481093&recommendMenuId=6329066038768779546&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_5630609209712787813");
                map.put("allNoFinishListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-2873821765078859725&formId=-6360989751442059508&type=baseInfo&tag=1728568481093&recommendMenuId=8412390701809944635&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-3938184848216340015");
                map.put("allExceedYpsListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-5974348102298022552&formId=-6360989751442059508&type=baseInfo&tag=1728568481093&recommendMenuId=-5735682116221713096&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-7005762314434426791");
                map.put("allNoExceedYpsListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-1726847328197655714&formId=-6360989751442059508&type=baseInfo&tag=1728569612318&recommendMenuId=4687815861709850365&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-4440750819975280985");
        }else{
                //测试环境
                map.put("allListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=8968451508401753564&formId=8583817712342617171&type=baseInfo&tag=1728554242057&recommendMenuId=-1813189009300435941&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-6641124620999638043");
                map.put("allFinishListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-2335789605972455665&formId=8583817712342617171&type=baseInfo&tag=1728554242057&recommendMenuId=-8291702771841873172&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-8563365629434640424");
                map.put("allNoFinishListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-780627910939950621&formId=8583817712342617171&type=baseInfo&tag=1728554242057&recommendMenuId=8678925535560944482&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_217219718185174035");
                map.put("allExceedYpsListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-8273751655401370178&formId=8583817712342617171&type=baseInfo&tag=1728554242057&recommendMenuId=1946882990056098502&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_7873783518196943880");
                map.put("allNoExceedYpsListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=1094575408962685272&formId=8583817712342617171&type=baseInfo&tag=1728554242057&recommendMenuId=-1172254944511703524&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-8544642631072134790");
            }
        }else{
            if (AppContext.getSystemProperty("brwh.sapCompanyDB").equals("CQBRWH_ZSK")){
                //正式环境
                map.put("allListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=8564296307921668445&formId=-4637401560272321472&type=baseInfo&tag=1728568481093&recommendMenuId=7989914863803253056&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-4287636999096087217");
                map.put("allFinishListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-1181160468750055428&formId=-4637401560272321472&type=baseInfo&tag=1728568481093&recommendMenuId=-6381313903695671511&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_801273241827559908");
                map.put("allNoFinishListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-4510816117732850095&formId=-4637401560272321472&type=baseInfo&tag=1728568481093&recommendMenuId=-8180523004370730949&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_2460166495256792081");
                map.put("allExceedYpsListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=395817343187108829&formId=-4637401560272321472&type=baseInfo&tag=1728568481093&recommendMenuId=1014704126201383429&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_2837100633709299448");
                map.put("allNoExceedYpsListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-8463075904442187366&formId=-4637401560272321472&type=baseInfo&tag=1728568481093&recommendMenuId=5029937260718604651&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-1800220205451886383");
            }else{
                //测试环境
                map.put("allListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=8244696295742065293&formId=-621242294833299201&type=baseInfo&tag=1728554242057&recommendMenuId=2805827541443823844&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_4227431595568612297");
                map.put("allFinishListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-6654359067271086047&formId=-621242294833299201&type=baseInfo&tag=1728554242057&recommendMenuId=3566749148522234443&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_5463180948569214106");
                map.put("allNoFinishListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-2917038860690502765&formId=-621242294833299201&type=baseInfo&tag=1728554242057&recommendMenuId=-1854196156132340640&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-8270518395710585313");
                map.put("allExceedYpsListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=5713462181393288585&formId=-621242294833299201&type=baseInfo&tag=1728554242057&recommendMenuId=-9169816672167375431&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_2415631048111704641");
                map.put("allNoExceedYpsListUrl","/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-8581460347406889029&formId=-621242294833299201&type=baseInfo&tag=1728554242057&recommendMenuId=-1768037656737592035&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-7280975004313039234");
            }
        }
        map.put("allNoExceedYpsList",allNoExceedYpsList.size());
        return map;

    }


        /**
         * 抽调出五个一中公共代码部分
         * 统计平均评审时间
         * @param formCode 模板编码
         * @param name 名称备注
         * @return
         * @throws BusinessException
         * @throws SQLException
         */
    public double getCalculateTime(String formCode,String name,String fileIdName) throws BusinessException, SQLException {
        // 提取当前年份
        int thisYear = LocalDate.now().getYear();
        // 设置日期为今年的第一天
        LocalDate startOfYear = LocalDate.of(thisYear, 1, 1);
        //今年最后一天
        LocalDate endOfYear = LocalDate.of(thisYear, 12, 31);
        String kssj = AppContext.getSystemProperty("ltc.ltc_kssj"); //开始时间
        String ltc_pjsj = AppContext.getSystemProperty(fileIdName); //平均时间
        //根据档案编码获取formBean对象
        FormBean FormBean = cap4FormManager.getFormByFormCode(formCode);
        List<FormFieldBean> formFieldBeans = FormBean.getMasterTableBean().getFields();
        StringBuilder stb = new StringBuilder();
        stb.append("select id, ");
        String ltc_pjsjFieldName="";
        String kssjFieldName="";
        for (FormFieldBean formFieldBean : formFieldBeans) {
            String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
            if (ltc_pjsj.equals(fieldDesc)) {
                ltc_pjsjFieldName = formFieldBean.getName();
            }
            if (kssj.equals(fieldDesc)){
                kssjFieldName = formFieldBean.getName();
            }
            if (!"".equals(fieldDesc)) {
                String fieldName = formFieldBean.getName();
                stb.append(fieldName).append(",");
            }
        }
        /* 删除最后一个逗号，完成SQL查询语句的构建 */
        String str = stb.substring(0, stb.length() - 1);
        String tableName = FormBean.getMasterTableBean().getTableName();
        String allCountByIdsSql= str + " from " + tableName
                +" where "+ kssjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"'"
                + " and " +ltc_pjsjFieldName +" > 0";
        log.info(name+"评审时间总数-----sql语句：" + allCountByIdsSql);
        //评审时间总数
        List allList = capDataListSqlHelpManager.selectDataList(allCountByIdsSql, null);
        int sumCount=allList.size();
        double pjsj=0.0;
        if (!allList.isEmpty()){
            for (int i = 0; i < allList.size(); i++) {
                Map<String, Object> dataMap = (Map<String, Object>) allList.get(i);
                if (dataMap.get(ltc_pjsjFieldName) == null|| "".equals(dataMap.get(ltc_pjsjFieldName))){
                    continue;
                }
                 pjsj += Double.parseDouble(dataMap.get(ltc_pjsjFieldName).toString());
            }
        }

        if (sumCount==0){
            return 0;
        }else {
                return roundToOneDecimalPlace((pjsj * 24) / sumCount);
        }
    }


    public List<Map<String,Object>> getxszjTimes() throws BusinessException, SQLException {
        List<Map<String,Object>> res=new ArrayList<>();
        // 提取当前年份
        int thisYear = LocalDate.now().getYear();
        // 设置日期为今年的第一天
        LocalDate startOfYear = LocalDate.of(thisYear, 1, 1);
        //今年最后一天
        LocalDate endOfYear = LocalDate.of(thisYear, 12, 31);
        String name="xszj_name";
        String kssj = AppContext.getSystemProperty("ltc.ltc_kssj"); //开始时间
        String ltc_pjsj = "ltcHtpslcDa_xszjDiffTime"; //平均时间
        //根据档案编码获取formBean对象
        FormBean FormBean = cap4FormManager.getFormByFormCode("LTC_HTPSDA_001");
        List<FormFieldBean> formFieldBeans = FormBean.getMasterTableBean().getFields();
        StringBuilder stb = new StringBuilder();
        stb.append("select id, ");
        String ltc_pjsjFieldName="";
        String kssjFieldName="";
        String nameField="";
        for (FormFieldBean formFieldBean : formFieldBeans) {
            String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
            if (ltc_pjsj.equals(fieldDesc)) {
                ltc_pjsjFieldName = formFieldBean.getName();
            }
            if (kssj.equals(fieldDesc)){
                kssjFieldName = formFieldBean.getName();
            }
            if (name.equals(fieldDesc)){
                nameField = formFieldBean.getName();
            }

        }
        /* 删除最后一个逗号，完成SQL查询语句的构建 */
//        String str = stb.substring(0, stb.length() - 1);
        String tableName = FormBean.getMasterTableBean().getTableName();
        String allCountByIdsSql=  "select sum("+ltc_pjsjFieldName+") as time, "
                +nameField+" as person ,count(1) as counts from " + tableName
                +" where "+ kssjFieldName +" between '"+startOfYear+"' and '"+endOfYear+"' and "
                +nameField+" is not null and "
                +ltc_pjsjFieldName+" >0 group by "+nameField ;

        log.info("销售总监评审时间总数-----sql语句：" + allCountByIdsSql);
        //评审时间总数
        List allList = capDataListSqlHelpManager.selectDataList(allCountByIdsSql, null);

        if (!allList.isEmpty()){
            for (int i = 0; i < allList.size(); i++) {
                Map<String, Object> dataMap = (Map<String, Object>) allList.get(i);
                V3xOrgMember v3xOrgMember = orgManager.getMemberById(Long.parseLong((String) dataMap.get("person")));
                String memberName = v3xOrgMember.getName();
                Map<String, Object> resMap = new HashMap<>();
                resMap.put("memberName", memberName);
                resMap.put("day", dataMap.get("time"));
                resMap.put("url", "/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&businessId=841955755423256384&moduleId=-7740745912921274483&formId=-4637401560272321472&type=baseInfo&tag=1728473601298&recommendMenuId=5609606026495936269&menuSummary=add&portalId=1&_resourceCode=000_/cap4/busi_-984500450664494687");
                resMap.put("hour",roundToOneDecimalPlace((Double.parseDouble(dataMap.get("time").toString()) * 24)/Integer.parseInt(dataMap.get("counts").toString())));
                res.add(resMap);
            }
        }

       return res;
    }

    /**
     * 项目成功率
     * @return 取自 重大项目利润金额 表单
     * @throws BusinessException
     */
    public String getCgl() throws Exception {

        LocalDate now = LocalDate.now(); // 获取当前日期
        LocalDate lastMonthDate = now.minusMonths(1); // 上个月的日期
        int lastYear = lastMonthDate.getYear(); // 上个月的年份
        int lastMonth = lastMonthDate.getMonthValue(); // 上个月的月份


        String formCode="ZDXM_LRJE";
        String zd_year="zd_year";
        String zd_month="zd_month";
        String zd_cgl="zd_cgl";
        //根据档案编码获取formBean对象
        FormBean formBean = cap4FormManager.getFormByFormCode(formCode);

        List<FormFieldBean> formFieldBeans = formBean.getMasterTableBean().getFields();
        StringBuilder stb = new StringBuilder();
        stb.append("select id, ");
        String zdYearFieldName="";
        String zdMonthFieldName="";
        String zdCglFieldName="";
        for (FormFieldBean formFieldBean : formFieldBeans) {
            String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
            if (zd_year.equals(fieldDesc)) {
                zdYearFieldName = formFieldBean.getName();
            }
            if (zd_month.equals(fieldDesc)){
                zdMonthFieldName = formFieldBean.getName();
            }
            if (zd_cgl.equals(fieldDesc)){
                zdCglFieldName = formFieldBean.getName();
            }
            if (!"".equals(fieldDesc)) {
                String fieldName = formFieldBean.getName();
                stb.append(fieldName).append(",");
            }
        }
        /* 删除最后一个逗号，完成SQL查询语句的构建 */
        String str = stb.substring(0, stb.length() - 1);
        String tableName = formBean.getMasterTableBean().getTableName();
        String sql= str + " from " + tableName
                + " where " + zdYearFieldName + " = "+lastYear+" and "
                + zdMonthFieldName + " = "+lastMonth+" and "+zdCglFieldName + " is not null";
        log.info("项目成功率-----sql语句：" + sql);
        //评审时间总数
        List allList = capDataListSqlHelpManager.selectDataList(sql, null);
        if (!allList.isEmpty()){
            Map<String, Object> dataMap = (Map<String, Object>) allList.get(0);
            if (dataMap.get(zdCglFieldName)!=null&&!dataMap.get(zdCglFieldName).toString().isEmpty()){
                double cgl=Double.parseDouble(dataMap.get(zdCglFieldName).toString())*100;
                // 使用 DecimalFormat 格式化为保留两位小数
                DecimalFormat decimalFormat = new DecimalFormat("0.00");
                String res = decimalFormat.format(cgl);
                return res;
            }else{
                return "0.00";
            }
        }else{
            return "0.00";
        }
    }

    /**
     * 根据项目编码获取销售订单双十或双十总订货金额
     * @param ids 项目编码集合
     * @param name 名称或备注
     * @return 返回以万为单位的金额且保留两位小数
     */
    public String getSumOfMoneyBySsOrFss(List<String> ids,String name,boolean isSs) throws BusinessException, SQLException {
        double sumOfMoney=0.0;
        if (ids.size()==0){
            ids.add("0");
        }
        StringBuilder sb = new StringBuilder("( '");
        for (int i = 0; i < ids.size(); i++) {
            sb.append(ids.get(i));
            if (i < ids.size() - 1) {
                sb.append("', '");
            }
        }
        sb.append("' )");
        String xsdd_Temp = AppContext.getSystemProperty("ltc.xsdd_Temp"); //模板编码
        String xsdd_ddje = AppContext.getSystemProperty("ltc.xsdd_ddje"); //订单金额
        String xmda_xmbm = AppContext.getSystemProperty("ltc.xmda_xmbm"); //项目编号
        String xsddda_ddzt=AppContext.getSystemProperty("ltc.xsddda_ddzt"); //订单状态
        String xsddda_tb_date=AppContext.getSystemProperty("ltc.xsddda_tb_date"); //填报日期
        String isXmxj="isSS"; //项目星级

        //枚举编码
        String xsddda_ddzt_sx = AppContext.getSystemProperty("ltc.xsddda_ddzt_sx"); //生效
        CtpEnumItem Enum_xsddda_ddzt_sx = FormCap4Kit.getEnumByCode(xsddda_ddzt_sx);
        String xsddda_ddzt_wj = AppContext.getSystemProperty("ltc.xsddda_ddzt_wj"); //完结
        CtpEnumItem Enum_xsddda_ddzt_wj = FormCap4Kit.getEnumByCode(xsddda_ddzt_wj);

        // 提取当前年份
        int thisYear = LocalDate.now().getYear();
        // 设置日期为今年的第一天
        LocalDate startOfYear = LocalDate.of(thisYear, 1, 1);
        //今年最后一天
        LocalDate endOfYear = LocalDate.of(thisYear, 12, 31);

        //根据档案编码获取formBean对象
        FormBean FormBean = cap4FormManager.getFormByFormCode(xsdd_Temp);
        List<FormFieldBean> formFieldBeans = FormBean.getMasterTableBean().getFields();
        StringBuilder stb = new StringBuilder();
        stb.append("select id, ");
        String xsddDdjeFieldName="";
        String xmdaXmbmFieldName="";
        String xsdddaDdztFieldName="";
        String xsdddaTbDateFieldName="";
        String xmxjFieldName="";
        for (FormFieldBean formFieldBean : formFieldBeans) {
            String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
            if (xsdd_ddje.equals(fieldDesc)) {
                xsddDdjeFieldName = formFieldBean.getName();
            }
            if (xmda_xmbm.equals(fieldDesc)){
                xmdaXmbmFieldName = formFieldBean.getName();
            }
            if (xsddda_ddzt.equals(fieldDesc)){
                xsdddaDdztFieldName = formFieldBean.getName();
            }
            if (xsddda_tb_date.equals(fieldDesc)){
                xsdddaTbDateFieldName = formFieldBean.getName();
            }
            if (isXmxj.equals(fieldDesc)){
                xmxjFieldName = formFieldBean.getName();
            }
            if (!"".equals(fieldDesc)) {
                String fieldName = formFieldBean.getName();
                stb.append(fieldName).append(",");
            }

        }
        //
        /* 删除最后一个逗号，完成SQL查询语句的构建 */
        String str = stb.substring(0, stb.length() - 1);
        String tableName = FormBean.getMasterTableBean().getTableName();
        String allDataListByXmbmSql= str + " from " + tableName
                +" where "+xsdddaDdztFieldName+" in ('"+Enum_xsddda_ddzt_sx.getId()+"','"+Enum_xsddda_ddzt_wj.getId()+"') and ("
                +xsdddaTbDateFieldName+" between '"+startOfYear+"' and '"+endOfYear+"') and not ("
                + xsdddaTbDateFieldName + " in ('2024-01-08', '2024-01-09', '2024-01-10')) and ";
        CtpEnumItem ssa = FormCap4Kit.getEnumByCode("lx4_3");
        CtpEnumItem ssb = FormCap4Kit.getEnumByCode("lx5_3");
        if (isSs==true){
            allDataListByXmbmSql +=xmxjFieldName+" in ('"+ssa.getId()+"' , '"+ssb.getId()+" ')";
        }else {
            allDataListByXmbmSql +=xmxjFieldName+" != '"+ssa.getId()+"' and "+xmxjFieldName+" != '"+ssb.getId()+" ' and "+xmxjFieldName +" is not null";
        }
        log.info(name+"-----sql语句：" + allDataListByXmbmSql);
        //获取双十或者非双十数据
        List allList = capDataListSqlHelpManager.selectDataList(allDataListByXmbmSql, null);
        if (!allList.isEmpty()){
            for (int i = 0; i < allList.size(); i++) {
                Map<String, Object> dataMap = (Map<String, Object>) allList.get(i);
                if (dataMap.get(xsddDdjeFieldName)!=""&&dataMap.get(xsddDdjeFieldName)!=null) {
                    sumOfMoney += Double.parseDouble(dataMap.get(xsddDdjeFieldName).toString());
                }
            }
        }
        DecimalFormat df = new DecimalFormat("0.00");
        return df.format(sumOfMoney/10000);
    }

    /**
     * 将给定的double值四舍五入到一位小数。
     * @param value 要处理的数值
     * @return 四舍五入后的结果
     */
    private static double roundToOneDecimalPlace(double value) {
        //现在要保留两位小数
        return Math.round(value * 100) / 100.0;
    }

    /**
     * 根据指定字段去重列表中的数据，并保持原有类型
     *
     * @param list           原始列表
     * @param fieldName      字段名称
     * @return 去重后的列表
     */
    private static List<Map<String, Object>> deduplicateByFieldName(List<Map<String, Object>> list, String fieldName) {
        Map<Object, Map<String, Object>> uniqueMap = new LinkedHashMap<>();

        // 遍历列表，根据 fieldName 进行去重
        for (Map<String, Object> map : list) {
            Object fieldValue = map.get(fieldName);
            if (!uniqueMap.containsKey(fieldValue)) {
                uniqueMap.put(fieldValue, map);
            }
        }

        // 重新构建结果列表
        return new ArrayList<>(uniqueMap.values());
    }
}
